php Laravel 4 查询生成器：LEFT JOIN ... AND ... 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17404743/
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 4 Query Builder: LEFT JOIN ... AND ... query
提问by user2324369
I'm trying to select all results from one table and merge with another table when user ID's match.
我试图从一个表中选择所有结果，并在用户 ID 匹配时与另一个表合并。
I have three tables: runs, users and a run_user pivot table. I want to select all results from 'runs' and additional columns in the pivot table (which are 'completed', 'sticky', 'last_tested' and 'difficulty'), but only pull data from run_user for the current user.
我有三个表：runs、users 和 run_user 数据透视表。我想从“运行”和数据透视表中的其他列（“已完成”、“粘性”、“最后测试”和“难度”）中选择所有结果，但仅从当前用户的 run_user 中提取数据。
In raw SQL I've managed to do this via a LEFT JOIN with an AND statement:
在原始 SQL 中，我通过带有 AND 语句的 LEFT JOIN 设法做到了这一点：
SELECT
runs.startpoint,
runs.startpostcode,
runs.endpoint,
runs.endpostcode,
run_user.completed,
run_user.sticky,
run_user.last_tested,
run_user.difficulty
FROM runs
LEFT JOIN run_user ON run_user.run_id=runs.id AND run_user.user_id = '2'
Any suggestions how to do this via the Query Builder? I can do the LEFT JOIN in Laravel 4 but can't figure out how to combine this with an AND statement as well.
任何建议如何通过查询生成器执行此操作？我可以在 Laravel 4 中执行 LEFT JOIN 但不知道如何将它与 AND 语句结合起来。
Any help is appreciated.
任何帮助表示赞赏。
Thanks!
谢谢！
回答by Alexandre Danault
As you requested, this is how you would do your query with the query builder:
按照您的要求，这是您使用查询构建器进行查询的方式：
DB::table('runs')
->leftJoin('run_user', function($join)
{
$join->on('run_user.run_id', '=', 'runs.id')
->on('run_user.user_id', '=', '2');
})
->get();
But that JOIN statement looks a bit weird, you probably want to turn that into a normal WHERE (unless you have a very specific reason to filter directly in the JOIN clause).
但是那个 JOIN 语句看起来有点奇怪，你可能想把它变成一个普通的 WHERE（除非你有一个非常具体的理由直接在 JOIN 子句中过滤）。
DB::table('runs')->join('run_user', 'run_user.run_id', '=', 'runs.id')->where('run_user.user_id', '=', '2')->get();
回答by user2324369
In case anyone is wondering I got this working using DB::raw on one of the leftJoin parameters:
如果有人想知道我在 leftJoin 参数之一上使用 DB::raw 得到了这个工作：
DB::table('runs')
->leftjoin('run_user', 'runs.id','=',
DB::raw('run_user.run_id AND run_user.user_id = ' . $user->id))
->get();
It's not as 'eloquent' as I'd like it to be but without a working 'andOn' condition this seems to be the only way if you want to add an AND to the LEFT JOIN.
它不像我希望的那样“雄辩”，但没有有效的“andOn”条件，如果您想向 LEFT JOIN 添加 AND，这似乎是唯一的方法。
If anyone can suggest a neater way I'd love to know!
如果有人可以建议一种更简洁的方法，我很想知道！
Thanks
谢谢
回答by Saurav Lal Karn
use this
用这个
DB::table('runs')->select('runs.startpoint','runs.startpostcode','runs.endpoint','runs.endpostcode','run_user.completed','run_user.sticky','run_user.last_tested','run_user.difficulty')
->leftJoin('run_user', function($join)
{
$join->on('run_user.run_id', '=', 'runs.id')
->where('run_user.user_id', '2');
})
->get();
回答by Trying Tobemyself
In Laravel 4 you can do this using Eloquent query. I assume that you are aware of Eloquent relationships and know to to create models. For your query it should be
在 Laravel 4 中，你可以使用 Eloquent 查询来做到这一点。我假设您了解 Eloquent 关系并知道如何创建模型。对于您的查询，它应该是
RunUser::where('user_id',$id)->leftjoin('runs','runs.id','=','run_user.run_id')->first();
NoteRunUser is the model for run_user table.
注意RunUser 是 run_user 表的模型。
回答by Hardik Hihoriya
Provident and user Table Join and get id to all details.
Provident 和用户表加入并获取所有详细信息的 ID。
$return = DB::table(config::get('databaseconstants.TBL_USER'))
->leftjoin('inex_pf_details', 'inex_users.id', '=', 'inex_pf_details.pf_user_id')
->select('inex_pf_details.*','inex_users.*')
->where('inex_pf_details.id', $id)
->first();
return $return;
回答by Denis Ch
Here is must work:
这是必须工作：
DB::table('runs')
->leftJoin('run_user', function($join) use ($user_id)
{
$join->on('run_user.run_id', '=', 'runs.id')
->where('run_user.user_id', '=', $user_id);
})
->get();
