mysql join ON and AND to laravel eloquent

I found that the where doesn't always work on the leftJoin clause

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.

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

When laravel eloquent just start getting complex like this

For more flexibility and readability I'll just use plain sql statement then hydrate the results.

$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`
";

$query = Person::hydrate(
    DB::select( $sql )
);