使用查询生成器或口才的具有附加条件的JOIN [英] A JOIN With Additional Conditions Using Query Builder or Eloquent
问题描述
我正在尝试通过Laravel查询生成器使用JOIN查询添加条件.
I'm trying to add a condition using a JOIN query with Laravel Query Builder.
<?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')
);
我知道我可以使用原始表达式,但随后会有SQL注入点.我已经使用查询生成器尝试了以下操作,但是生成的查询(显然是查询结果)不是我想要的:
I know I can use Raw Expressions but 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:
$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();
这是Laravel生成的查询:
This is the generated query by 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
如您所见,查询输出不具有该结构(尤其是在JOIN范围内).是否可以在JOIN下添加其他条件?
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?
如何使用Laravel的查询生成器构建相同的查询(如果可能的话),使用Eloquent更好还是应该保留在DB :: select上?
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?
推荐答案
$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();
不确定是否可以在laravel中将between子句添加到联接中.
Not quite sure if the between clause can be added to the join in laravel.
注释:
-
DB::raw()
指示Laravel不要回引用. - 通过将闭包传递给联接方法,您可以为其添加更多的联接条件,
on()
将添加AND
条件,而orOn()
将添加OR
条件.
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 addAND
condition andorOn()
will addOR
condition.
这篇关于使用查询生成器或口才的具有附加条件的JOIN的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!