taekni taekni - 1 year ago 105
MySQL Question

mysql join ON and AND to laravel eloquent

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.

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

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

Answer Source

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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download