How to concatenate columns with Laravel 4 Eloquent?

Question

I have a table called tenantdetails which contains

Tenant_Id | First_Name | Last_Name | ........

and I want to retrieve First_Name and Last Name as one column via the concatenation function of MySQL. So I write in my controller as follows

$tenants = Tenant::orderBy('First_Name')->lists('CONCAT(`First_Name`," ",`Last_Name`)','Tenant_Id');

But results from the following error:

 SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '`," ",`First_Name`)`, `Id` from `tenantdetails` order by `F' at line 1 (SQL: select `CONCAT(`First_Name`," ",`Last_Name`)`, `Id` from `tenantdetails` order by `First_Name` asc).

How can we avoid the backticks while calling a function of MySQL in Laravel Eloquent? I am interested only in Eloquent (not in fluent query). Thanks in advance.

Update

Thanks to @Andreyco for helping me. We can achieve this in a more elegant way using Laravel models, as below:

In our model:

public function getTenantFullNameAttribute() { return $this->attributes['First_Name'] .' '. $this->attributes['Last_Name']; }

and in our controller:

$tenants = Tenant::orderBy('First_Name')->get(); $tenants = $tenants->lists('TenantFullName', 'Tenant_Id');

This model solution is awesome. Is this in the documentation anywhere? — Kyle Ridolfo
– Kyle Ridolfo, Commented Nov 30, 2014 at 3:19
@Kyle Ridolfo. Thanks. Actually I forget the source for the solution. But I am sure it is not on the documentation until that day which I searched for the solution. — manuthalasseril
– manuthalasseril, Commented Nov 30, 2014 at 4:16
If anyone is still interested, documentation is here: laravel.com/docs/8.x/eloquent-mutators#accessors-and-mutators (this points to the latest Laravel version, you need to check out the version used in your project). — trane77
– trane77, Commented Oct 7, 2021 at 21:55

Andreyco · Accepted Answer · 2014-02-25 08:34:28Z

73

Tenant::select('Tenant_Id', DB::raw('CONCAT(First_Name, " ", Last_Name) AS full_name')) ->orderBy('First_Name') ->lists('full_name', 'Tenant_Id');

answered Feb 25, 2014 at 8:34

Andreyco

22.9k5 gold badges63 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

manuthalasseril Over a year ago

Is any possibility to do this only with eloquent orm

manuthalasseril Over a year ago

I am a beginner to laravel. I think it is combination of Fluent Query Builder with Eloquent. Am I wrong?

manuthalasseril Over a year ago

Thanks, this is enough for me. I just asked for to know whether we can do it only by eloquent or not (because now I am under learning process). Thanks for the help..

Alexander Taubenkorb Over a year ago

In Laravel 5 you have to either use DB; or call it with a backslash: \DB::raw...

simonhamp Over a year ago

@manuthalasseril I went with the selectRaw method suggested by @michel-ayres, but put it in a query scope (laravel.com/docs/4.2/eloquent#query-scopes) for simple re-use... makes it look more "Eloquent" :)

|

Michel Ayres · Accepted Answer · 2015-04-07 19:19:17Z

An easy way is to use selectRaw. It was implemented by Tailor in Jan 30, 2014

Source

Tenant::selectRaw('CONCAT(First_Name, " ", Last_Name) as TenantFullName, id')->orderBy('First_Name')->lists('TenantFullName', 'id'))

Phailee Sharma · Accepted Answer · 2024-08-17 02:31:16Z

lists() method used to select column from selected result. So first contact first name and last name and give this column with new alias name in select statement

 $tenants = Tenant::orderBy('First_Name')->select(DB::raw('CONCAT(`First_Name`," ",`Last_Name`) as name'),'Tenant_Id')->lists('name', 'id');

then you can select this alias in lists() method

just a note on the typo DB::row should be DB::raw, you may want to edit your answer.

Majbah Habib · Accepted Answer · 2016-08-01 10:46:14Z

You should use the DB::raw() to concat those of field

Tenant::select( 'Tenant_Id', DB::raw('CONCAT(First_Name,"-",Last_Name) as full_name') ) ->orderBy('First_Name') ->lists('full_name', 'Tenant_Id');

Sudaba Solaimankhil · Accepted Answer · 2021-02-03 08:49:12Z

You can also use the Query Builder to do this like:

DB::table('Tenant') ->selectRaw('CONCAT(First_Name, " - ", Last_Name) as fullName, id') ->get();

Hope be helpful :)

Collectives™ on Stack Overflow

How to concatenate columns with Laravel 4 Eloquent?

5 Answers 5

6 Comments

Comments

1 Comment

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

6 Comments

Comments

1 Comment

Comments

Comments

Linked

Related