Laravel query builder left outer join with where

2
votes

I would like to make a left outer join with laravel query builder with a where condition. I have 3 tables:

  • user (id, name, ..)
  • route (id, name, ..)
  • user_route (id, user_id, route_id)

I want to select all routes which aren't listed in the user_route table only for the specific user.

What I've tried:

$q = DB::table('route')
    ->join('user_route', 'route.id', '=', user_route.route_id', 'left outer')
    ->where('user_route.user_id', '!=', $filter->user);

This will return no rows.

$q = DB::table('route')
->leftJoin('user_route', function($join) use ($filter)
      {
           $join->on('route.id', '=', 'user_route.route_id')
                ->where('user_route.user_id', '=', DB::raw($filter->user));
      });

This will return all the routes as it is not an outer join.

Also tried something like this:

$q = DB::table('route')
->join('user_route', function($join) use ($filter)
      {
           $join->on('route.id', '=', 'user_route.route_id')
                ->where('user_route.user_id', '=', DB::raw($filter->user));
      }, 'left outer');

or

$q = DB::table('route')
->join('user_route', function($join) use ($filter)
      {
           $join->on('route.id', '=', 'user_route.route_id', 'left outer')
                ->where('user_route.user_id', '=', DB::raw($filter->user));
      });

None of them worked.

I've made an ugly workaround, where I pick all routes with left join, and in PHP I delete routes where user_id is not NULL (eg. exists).

Does anyone know how to make such query without doing it in SQL and passing it to the Eloquent (or what is the name of the DB plugin for Laravel)?

2
phplaravellaravel-5eloquent
Look at laravel.com/docs/5.3/eloquent-relationships – Ilya Yaremchuk

2 Answers

2
votes

Have a look at Eloquent's whereDoesntHave() method. It lets you filter out records that don't have a record in related table.

In your case, you want to do the following: for given user, get all routes that are not related to that user. The following code should do the trick:

// $userId contains the ID of the user for which you want to get the unrelated routes
$routes = Route::whereDoesntHave('users', function($query) use ($userId) {
  $query->where('id', '!=', $userId);
})->get();

Just make sure that you have users relation defined in your Route model.

-1
votes

It looks like you never actually call the "get" method. Try this:

<?php

$userid = 10;
$result = DB::table('route')
->join('user_route', 'route.id', '=', 'user_route.route_id')
->select('*')
->where('user_route.user_id', '=', $userid)
->get();

?>

Note you will need to add the correct keys based on your table. This would be easier if we could see your data structure.