mysql加入ON和AND到laravel雄辩 [英] mysql join ON and AND to laravel eloquent

问题描述

我已经能够使用以下原始sql获取我需要的查询结果：

I've been able to get the query result I need using the following raw 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.

根据一些类似的答案，我试过在 - > where（）或 leftJoin（） ，但每个人的操作的计数尚未被 $ user 过滤。如下：

Based on some similar answers, I'd tried functions within ->where() or leftJoin(), but the count of each person's actions isn't yet being filtered by $user. As it stands:

$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 roughly the right direction, right...?

如果相关， Persons.php 模型有两个 / code>关系：

If it's relevant, the Persons.php model has two actions relationships:

public function actions()
{
    return $this->hasMany('Action');
}

public function actionsUser($id)
{
    return $this->hasMany('Action')->where('user_id', $id);
}

推荐答案

所以，为了参考，解决它像这样：

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

- > where（） clause在 leftJoin 中，奇怪的是，需要通过正确传递sql查询的变量的语音标记（同样，2似乎不工作，而2 ）。

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

这篇关于mysql加入ON和AND到laravel雄辩的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！