Concat columns using “with()” function in Laravel Eloquent

Question

I'm trying to concat two columns from different tables into one single column.

$user = User::with(array('Person'=>function($query){ $query->selectRaw('CONCAT(prefix_person.name, " - ", prefix_user.code) as name, prefix_user.id'); }))->lists('name', 'id');

In my person class I have this method:

public function User() { return $this->hasOne('User'); }

And in my user class I have this one:

public function Person() { return $this->belongsTo('Person', 'person_id'); }

I get the following error:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'name' in 'field list' (SQL: select `name`, `id` from `prefix_user`)

When I tried

$user = User::with(array('Person'=>function($query){ $query->selectRaw('CONCAT(prefix_person.name, " - ", prefix_user.code) as name, prefix_user.id')->lists('name', 'id'); }));

I got this error:

error

I have used the selectRaw a couple of times, but never needed it into a with (join).

Can't you use an acessor that will concat it instead? It would make your logic easier to understand and to scale. — Ravan Scafi
– Ravan Scafi, Commented Apr 8, 2015 at 19:22
@Ravan Acessors are nice, but I don't know how these would work with lists and joins. Could you provide some example? — Michel Ayres
– Michel Ayres, Commented Apr 8, 2015 at 19:26

Ravan Scafi · Accepted Answer · 2015-04-09 13:14:40Z

The issue is that Eloquent will first query the users table, and only after, the persons table, so one query is not aware of the other and thus concatenating will not work.

You can use the Query Builder to do this using a join. It will be something like it:

$user = DB::table('users as u') ->join('persons as p', 'p.id', '=', 'u.person_id') ->selectRaw('CONCAT(p.name, " - ", u.code) as concatname, u.id') ->lists('concatname', 'u.id');

EDIT: And, as suggested by @michel-ayres comment, as long as you have an acessor to the field:

public function getFullNameAttribute() { return $this->attributes['name'] . ' - ' . $this->attributes['code']; }

you can use your own model to perform the join and listing:

User::join('person','person.id','=','user.person_id') ->select('person.name', 'user.code', 'user.id') ->get() ->lists('full_name', 'id');

Eeyup, you got to the root of my problem :/ I followed your first suggestion and create an acessor within my class, following this example at laravel's forum. I still think is a little ugly (if this work as linq does, then it calls 2x the query), but uses Eloquent at least. Here is the final:
User::join('person','person.id','=','user.person_id')->orderBy('person.name', 'ASC')->select('person.name', 'user.code', 'user.id')->get()->lists('full_name', 'id'); and in the Model I have a method public function getFullNameAttribute() { return $this->attributes['name'] . ' - ' . $this->attributes['code']; }
If possible, add (don't change) to your answer the comment too =) thanks
@MichelAyres I guess that you can get rid of the ->get() entirely. Can you test it to see if it's correct so that I can update the answer?
I tested without the ->get() and got an error saying that I don't have full_name. I think it needed it to fill up the function getFullNameAttribute, so I can use it on the lists.

Majbah Habib · Accepted Answer · 2016-08-01 11:03:33Z

You can solve it simply by using simple query,

User::join('persons as p', 'p.id', '=', 'users.person_id') ->get([ 'id', DB::raw('CONCAT(p.name,"-",users.code) as name') ]) ->lists('name', 'id');

Or, see another way

User::join('persons as p', 'p.id', '=', 'users.person_id') ->select( 'id', DB::raw('CONCAT(p.name,"-",users.code) as name') ) ->lists('name', 'id');

Faridul Khan · Accepted Answer · 2022-09-29 09:41:50Z

You can solve it by using this sample query

->when($request->value != null, fn ($q) => $q->where(DB::raw("CONCAT(col1,'',col2)"), '=', '' . $request->value. ''))

Collectives™ on Stack Overflow

Concat columns using “with()” function in Laravel Eloquent

3 Answers 3

6 Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

Comments

Comments

Related