Laravel 5 在 BETWEEN 中使用 OR 条件

谁能帮我在 laravel Eloquent 中构建以下查询，我真的很困惑使用 OR 条件和 between

SELECT * FROM tbl WHEREexisting_start BETWEEN $newStart AND $newEnd 或$newStart BETWEEN existing_start AND existing_end

我尝试使用像

whereBetween('existing_start',[$newStart,$newEnd])

但不知道如何使用 OR

解决方案

查询生成器提供了一个 orWhereBetween 方法，但它在 查询生成器文档.但是，您可以在 Laravel API 文档 中找到它.

<小时>

以下说明假设变量具有以下值:

$newStart = '1';$newEnd = '10';

<小时>

不幸的是，使用 orWhereBetween 作为第二个条件不适用于您的情况，因为 whereBetween 和 orWhereBetween 都会检查列值介于两个输入值之间.这对您的第一个条件来说很好，因为它会检查 existing_start 列值是否在 $newStart 和 $newEnd 之间.所以这很好:

->whereBetween('existing_start', [$newStart, $newEnd])

因为它将被编译为:

WHERE `existing_start` BETWEEN '1' 和 '10'

但是您的第二个条件想要检查来自 $newStart 的输入值是否在两列值 existing_start 和 existing_end 之间，并且有没有这样做的查询生成器方法.所以这行不通:

->orWhereBetween($newStart, ['existing_start', 'existing_end'])

因为它会被编译为:

OR `1` BETWEEN 'existing_start' AND 'existing_end'

注意1周围的反引号`，因为MySQL会尝试查找名为1的列并抛出错误.><小时>

所以这里最好的选择是使用 orWhereRaw 和这样的绑定:

DB::table('tbl')->whereBetween('existing_start', [$newStart, $newEnd])->orWhereRaw('? BETWEEN existing_start AND existing_end', [$newStart])->get();

? 将被替换为 $newStart 的值，该值将被正确引用和转义以避免 SQL 注入.

<小时>

当然，也可以选择使用两个分组条件来检查边界，这相当于您的 BETWEEN 条件:

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`在哪里`existing_start` BETWEEN '1' 和 '10' 或(`existing_start` <='1' AND `existing_end` >='1')

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

i tried using like

whereBetween('existing_start',[$newSTart,$newEnd])

but have no idea how to use OR

解决方案

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';

---

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])

As it will be compiled to:

WHERE `existing_start` BETWEEN '1' AND '10'

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'])

Because it will be compiled to:

OR `1` BETWEEN 'existing_start' AND 'existing_end'

Notice the backticks ` around 1, because of that MySQL will try to find a column named 1 and throw an error.

---

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();

The ? will be replaced by the value of $newStart which will be properly quoted and escaped to avoid SQL injection.

---

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();

Which will compile to:

SELECT * FROM `tbl`
WHERE
  `existing_start` BETWEEN '1' AND '10' OR
  (`existing_start` <= '1' AND `existing_end` >= '1')