laravel 使用查询生成器或 Eloquent 的附加条件的 JOIN
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16848987/
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
A JOIN With Additional Conditions Using Query Builder or Eloquent
提问by dede
I'm trying to add a condition using a JOIN query with Laravel Query Builder.
我正在尝试使用带有 Laravel Query Builder 的 JOIN 查询添加条件。
<?php
$results = DB::select('
SELECT DISTINCT
*
FROM
rooms
LEFT JOIN bookings
ON rooms.id = bookings.room_type_id
AND ( bookings.arrival between ? and ?
OR bookings.departure between ? and ? )
WHERE
bookings.room_type_id IS NULL
LIMIT 20',
array('2012-05-01', '2012-05-10', '2012-05-01', '2012-05-10')
);
I know I can use Raw Expressionsbut then there will be SQL injection points. I've tried the following with Query Builder but the generated query (and obviously, query results) aren't what I intended:
我知道我可以使用原始表达式，但是会有 SQL 注入点。我已经使用 Query Builder 尝试了以下操作，但生成的查询（显然，查询结果）不是我想要的：
$results = DB::table('rooms')
->distinct()
->leftJoin('bookings', function ($join) {
$join->on('rooms.id', '=', 'bookings.room_type_id');
})
->whereBetween('arrival', array('2012-05-01', '2012-05-10'))
->whereBetween('departure', array('2012-05-01', '2012-05-10'))
->where('bookings.room_type_id', '=', null)
->get();
This is the generated query by Laravel:
这是 Laravel 生成的查询：
select distinct * from `room_type_info`
left join `bookings`
on `room_type_info`.`id` = `bookings`.`room_type_id`
where `arrival` between ? and ?
and `departure` between ? and ?
and `bookings`.`room_type_id` is null
As you can see, the query output doesn't have the structure (especially under JOIN scope). Is it possible to add additional conditions under the JOIN?
如您所见，查询输出没有结构（尤其是在 JOIN 范围内）。是否可以在 JOIN 下添加附加条件？
How can I build the same query using Laravel's Query Builder (if possible) Is it better to use Eloquent, or should stay with DB::select?
如何使用 Laravel 的 Query Builder 构建相同的查询（如果可能）是使用 Eloquent 更好，还是应该继续使用 DB::select？
回答by Abishek
$results = DB::table('rooms')
->distinct()
->leftJoin('bookings', function($join)
{
$join->on('rooms.id', '=', 'bookings.room_type_id');
$join->on('arrival','>=',DB::raw("'2012-05-01'"));
$join->on('arrival','<=',DB::raw("'2012-05-10'"));
$join->on('departure','>=',DB::raw("'2012-05-01'"));
$join->on('departure','<=',DB::raw("'2012-05-10'"));
})
->where('bookings.room_type_id', '=', NULL)
->get();
Not quite sure if the between clause can be added to the join in laravel.
不太确定是否可以将 between 子句添加到 laravel 的连接中。
Notes:
笔记：
DB::raw()instructs Laravel not to put back quotes.- By passing a closure to join methods you can add more join conditions to it,
on()will addANDcondition andorOn()will addORcondition.
DB::raw()指示 Laravel 不要放回引号。- 通过将闭包传递给连接方法，您可以向其添加更多连接条件、
on()将添加AND条件和orOn()将添加OR条件。
回答by rickywiens
You can replicate those brackets in the left join:
您可以在左连接中复制这些括号：
LEFT JOIN bookings
ON rooms.id = bookings.room_type_id
AND ( bookings.arrival between ? and ?
OR bookings.departure between ? and ? )
is
是
->leftJoin('bookings', function($join){
$join->on('rooms.id', '=', 'bookings.room_type_id');
$join->on(DB::raw('( bookings.arrival between ? and ? OR bookings.departure between ? and ? )'), DB::raw(''), DB::raw(''));
})
You'll then have to set the bindings later using "setBindings" as described in this SO post: How to bind parameters to a raw DB query in Laravel that's used on a model?
然后，您必须稍后使用“setBindings”设置绑定，如本文中所述： 如何将参数绑定到 Laravel 中用于模型的原始数据库查询？
It's not pretty but it works.
它不漂亮，但它有效。
回答by vroldan
If you have some params, you can do this.
如果你有一些参数，你可以这样做。
$results = DB::table('rooms')
->distinct()
->leftJoin('bookings', function($join) use ($param1, $param2)
{
$join->on('rooms.id', '=', 'bookings.room_type_id');
$join->on('arrival','=',DB::raw("'".$param1."'"));
$join->on('arrival','=',DB::raw("'".$param2."'"));
})
->where('bookings.room_type_id', '=', NULL)
->get();
and then return your query
然后返回您的查询
return $results;
返回 $results;
回答by Majbah Habib
The sql query sample like this
像这样的 sql 查询示例
LEFT JOIN bookings
ON rooms.id = bookings.room_type_id
AND (bookings.arrival = ?
OR bookings.departure = ?)
Laravel join with multiple conditions
Laravel 加入多个条件
->leftJoin('bookings', function($join) use ($param1, $param2) {
$join->on('rooms.id', '=', 'bookings.room_type_id');
$join->on(function($query) use ($param1, $param2) {
$query->on('bookings.arrival', '=', $param1);
$query->orOn('departure', '=',$param2);
});
})
回答by Abid Ali
I am using laravel5.2 and we can add joins with different options, you can modify as per your requirement.
我正在使用 laravel5.2，我们可以添加具有不同选项的连接，您可以根据您的要求进行修改。
Option 1:
DB::table('users')
->join('contacts', function ($join) {
$join->on('users.id', '=', 'contacts.user_id')->orOn(...);//you add more joins here
})// and you add more joins here
->get();
Option 2:
$users = DB::table('users')
->join('contacts', 'users.id', '=', 'contacts.user_id')
->join('orders', 'users.id', '=', 'orders.user_id')// you may add more joins
->select('users.*', 'contacts.phone', 'orders.price')
->get();
option 3:
$users = DB::table('users')
->leftJoin('posts', 'users.id', '=', 'posts.user_id')
->leftJoin('...', '...', '...', '...')// you may add more joins
->get();
回答by Jason Lewis
There's a difference between the raw queries and standard selects (between the DB::rawand DB::selectmethods).
原始查询和标准选择（在DB::raw和DB::select方法之间）之间存在差异。
You can do what you want using a DB::selectand simply dropping in the ?placeholder much like you do with prepared statements (it's actually what it's doing).
你可以使用 a 做你想做DB::select的?事情，就像使用准备好的语句一样简单地放入占位符（这实际上是它在做什么）。
A small example:
一个小例子：
$results = DB::select('SELECT * FROM user WHERE username=?', ['jason']);
The second parameter is an array of values that will be used to replace the placeholders in the query from left to right.
第二个参数是一个值数组，用于从左到右替换查询中的占位符。
