Laravel 5将OR条件与BETWEEN一起使用 [英] Laravel 5 using OR condition with BETWEEN
问题描述
有人可以帮我在laravel中建立下面的查询吗?口才我真的很困惑使用 OR 条件和之间
Hi can anyone help me building below query in laravel Eloquent i am really confuse in using OR condition with between
SELECT * FROM tbl WHERE
existing_start BETWEEN $newSTart AND $newEnd OR
$newStart BETWEEN existing_start AND existing_end
我尝试使用
whereBetween('existing_start',[$newSTart,$newEnd])
但不知道如何使用OR
but have no idea how to use OR
推荐答案
查询生成器提供了一种orWhereBetween
方法,但 Laravel API文档中找到它.
There is an orWhereBetween
method available from the Query Builder, but it is undocumented in the Query Builder Documentation. You can however find it in the Laravel API Documentation.
以下说明假定变量具有以下值:
The explanations below assume that the variables have the following values:
$newStart = '1';
$newEnd = '10';
不幸的是,将orWhereBetween
用作第二个条件不适用于您的情况,因为whereBetween
和orWhereBetween
都将检查列值是否在两个输入值之间.从您的第一个条件开始就可以了,因为它会检查existing_start
列的值是否在$newStart
和$newEnd
之间.这样就可以了:
Unfortunatelly, using orWhereBetween
for the second condition is not applicable in your case, because both whereBetween
and orWhereBetween
will check if a column value is between two input values. This is fine from your first condition since it checks if the existing_start
column value is between $newStart
and $newEnd
. So this is fine:
->whereBetween('existing_start', [$newStart, $newEnd])
它将被编译为:
WHERE `existing_start` BETWEEN '1' AND '10'
但是,您的第二个条件要检查$newStart
的输入值是否在两个列值existing_start
和existing_end
之间,并且没有Query Builder方法可以执行此操作.所以这行不通:
However your second condition wants to check if an input value from $newStart
is between two column values existing_start
and existing_end
, and there is no Query Builder method that does that. So this will not work:
->orWhereBetween($newStart, ['existing_start', 'existing_end'])
因为它将被编译为:
OR `1` BETWEEN 'existing_start' AND 'existing_end'
请注意1
周围的反引号`
,因为MySQL会尝试查找名为1
的列并抛出错误.
Notice the backticks `
around 1
, because of that MySQL will try to find a column named 1
and throw an error.
所以最好的选择是将orWhereRaw
与这样的绑定一起使用:
So the best option here is to use orWhereRaw
with bindings like this:
DB::table('tbl')
->whereBetween('existing_start', [$newStart, $newEnd])
->orWhereRaw('? BETWEEN existing_start AND existing_end', [$newStart])
->get();
?
将替换为$newStart
的值,该值将正确加引号并转义以避免SQL注入.
The ?
will be replaced by the value of $newStart
which will be properly quoted and escaped to avoid SQL injection.
或者当然,总是可以选择两个分组条件来检查边界,这等同于您的BETWEEN
条件:
Or course there is always the option of having two grouped conditions that check the boundaries, which would be equivalent to your BETWEEN
condition:
DB::table('tbl')
->whereBetween('existing_start', [$newStart, $newEnd])
->orWhere(function ($query) use ($newStart) {
$query->where('existing_start', '<=', $newStart);
$query->where('existing_end', '>=', $newStart);
})->get();
将编译为:
SELECT * FROM `tbl`
WHERE
`existing_start` BETWEEN '1' AND '10' OR
(`existing_start` <= '1' AND `existing_end` >= '1')
这篇关于Laravel 5将OR条件与BETWEEN一起使用的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!