在 Laravel Eloquent 中使用“with()”函数连接列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/29523345/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Concat columns using “with()” function in Laravel Eloquent
提问by Michel Ayres
I'm trying to concattwo columns from different tables into one single column.
我试图Concat的两个不同表中的列到一个单独的列。
$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 classI have this method:
在我的person class我有这个方法：
public function User()
{
return $this->hasOne('User');
}
And in my user classI have this one:
在我的user class我有这个：
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:
我收到此错误：
I have used the selectRawa couple of times, but never needed it into a with (join).
我已经使用了selectRaw几次，但从未需要将其放入 with (join) 中。
回答by Ravan Scafi
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.
问题是 Eloquent 将首先查询用户表，然后才查询人员表，因此一个查询不知道另一个查询，因此连接将不起作用。
You can use the Query Builderto do this using a join. It will be something like it:
您可以使用查询生成器通过join. 它会是这样的：
$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:
编辑：而且，正如@michel-ayres 评论所建议的那样，只要您有该领域的访问者：
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');
回答by Majbah Habib
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');
