Michel Ayres Michel Ayres - 4 months ago 64
SQL Question

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

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).

Answer

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