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

12

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');

edited Apr 9, 2015 at 13:14

answered Apr 8, 2015 at 19:43

Ravan Scafi

6,3922 gold badges26 silver badges32 bronze badges

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

6 Comments

Michel Ayres Over a year ago

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:

Michel Ayres Over a year ago

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']; }

Michel Ayres Over a year ago

If possible, add (don't change) to your answer the comment too =) thanks

Ravan Scafi Over a year ago

@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?

Michel Ayres Over a year ago

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

2

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');

answered Aug 1, 2016 at 11:03

Majbah Habib

8,6064 gold badges41 silver badges42 bronze badges

Comments

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

0

You can solve it by using this sample query

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

answered Sep 29, 2022 at 9:41

Faridul Khan

2,0571 gold badge20 silver badges29 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

6 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related