Is there an Eloquent way of doing a leftjoin in Laravel 5.4

Question

Is there a eloquent way to do a left join in Laravel?

We'd like to get all games and fill in the progress for each one if it exists in user_games.

Right now we've written the following solution, however this isn't eloquent at all, which we like it to be.

public function usergames($user_id) { return DB::table('games')->leftJoin('user_games', function ($join) use ($user_id) { $join->on('user_games.game_id', '=', 'games.id')->where('user_games.user_id', '=', $user_id); })->get(); }

DB model:

Thanks in advance!

musicvicious musicvicious · Accepted Answer · 2017-07-03T08:36:37

A way to do this without you actually writing a left/inner join is to use the eloquent relationships.

In your case you will have 2 model classes: User and Game

class User extends Model {
    public function games() {
        return $this->belongsToMany(App\Game::class);
    }
}

Now, you can access the user's games like so:

$user = App\User::find($user_id);
$usergames = $user->games; // Illuminate\Support\Collection

If you want to get a list of users with games, then look into eager loading. That would look something like this:

User::with('games')->get();

This way, Eloquent will know to lazy load the relationship meaning it will only run 2 queries. One to grab the users. and one to grab the games associated with the user, and then make them available for you in the 'games' property of the user object.

Is there an Eloquent way of doing a leftjoin in Laravel 5.4

1 Answers