Laravel查询构建器将外部联接留在哪里

Question

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

Answer 1

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.