mysql 加入 ON 和 AND 到 Laravel 雄辩
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27460945/
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
mysql join ON and AND to laravel eloquent
提问by taekni
I've been able to get the query result I need using the following raw sql:
我已经能够使用以下原始 sql 获得我需要的查询结果：
select `person`.`id`, `full_name`, count(actions.user_id) as total
from `persons`
left join `actions`
on `actions`.`person_id` = `persons`.`id`
and `actions`.`user_id` = $user
where `type` = 'mp'
group by `persons`.`id`
But I haven't been able to get it working in eloquent yet.
但我还没有能够让它在 eloquent 中工作。
Based on some similar answers, I'd tried functions within ->where()or leftJoin(), but the countof each person's actions isn't yet being filtered by $user. As it stands:
基于一些类似的答案，我尝试了->where()或 中的函数leftJoin()，但count每个人的动作的 尚未被 过滤$user。就目前而言：
$query = Person::leftJoin('actions', function($q) use ($user)
{
$q->on('actions.person_id', 'persons.id')
->where('actions.user_id', $user);
})
->groupBy('persons.id')
->where('type', 'foo')
//->where('actions.user_id', '=', $user)
->get(['persons.id', 'full_name', DB::raw('count(actions.id) as total')]);
I'm at least heading in roughlythe right direction, right...?
我至少在标题大致正确的方向，正确的...？
If it's relevant, the Persons.phpmodel has two actionsrelationships:
如果相关，Persons.php模型有两个actions关系：
public function actions()
{
return $this->hasMany('Action');
}
public function actionsUser($id)
{
return $this->hasMany('Action')->where('user_id', $id);
}
回答by taekni
So, for reference, I solved it like so:
所以，作为参考，我是这样解决的：
$query = Person::leftJoin('actions', function($q) use ($user)
{
$q->on('actions.person_id', '=', 'persons.id')
->where('actions.user_id', '=', "$user");
})
->groupBy('persons.id')
->where('type', 'foo')
->get(['persons.id', 'full_name', DB::raw('count(actions.id) as total')]);
The ->where()clause within leftJoin, oddly, needs the speech marks for the variable to be passed through the sql query correctly (likewise, '2' doesn't seem to work while "2" does).
奇怪的是， 中的->where()子句leftJoin需要变量的语音标记才能正确地通过 sql 查询（同样，“2”似乎不起作用而“2”起作用）。
回答by Carla Sousa
I found that the wheredoesn't always work on the leftJoinclause
我发现where并不总是适用于该leftJoin条款
If in the future you get any trouble with it, I'd suggest you using this:
如果将来你遇到任何问题，我建议你使用这个：
$query = Person::leftJoin('actions', function($q) use ($user)
{
$q->on('actions.person_id', '=', 'persons.id')
->on('actions.user_id', '=', "$user");
})
->groupBy('persons.id')
->where('type', 'foo')
->get(['persons.id', 'full_name', DB::raw('count(actions.id) as total')]);
Hope it helps someone.
希望它可以帮助某人。
