Laravel 查询生成器左外连接与 where
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/40272072/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Laravel query builder left outer join with where
提问by Jozef Kétyi
I would like to make a left outer join with laravel query builder with a where condition. I have 3 tables:
我想使用带有 where 条件的 laravel 查询构建器进行左外连接。我有3张桌子：
- user (id, name, ..)
- route (id, name, ..)
- user_route (id, user_id, route_id)
- 用户（ID，姓名，..）
- 路线（ID，名称，..）
- 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.
我只想为特定用户选择 user_route 表中未列出的所有路由。
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).
我做了一个丑陋的解决方法，我选择所有带有左连接的路由，并在 PHP 中删除 user_id 不为 NULL 的路由（例如存在）。
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)?
有谁知道如何在不使用 SQL 并将其传递给 Eloquent（或 Laravel 的 DB 插件的名称是什么）的情况下进行此类查询？
回答by jedrzej.kurylo
Have a look at Eloquent's whereDoesntHave()method. It lets you filter out records that don't have a record in related table.
看看Eloquent的whereDoesntHave()方法。它可以让您过滤掉相关表中没有记录的记录。
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 usersrelation defined in your Routemodel.
只需确保您在Route模型中定义了用户关系。
回答by Rivers
It looks like you never actually call the "get" method. Try this:
看起来您从未真正调用过“get”方法。尝试这个：
<?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.
请注意，您需要根据您的表添加正确的键。如果我们可以看到您的数据结构，这会更容易。
