Laravel 5 在 BETWEEN 中使用 OR 条件

Laravel 5 using OR condition with BETWEEN

提问by sanu

Hi can anyone help me building below query in laravel Eloquent i am really confuse in using ORcondition with between

嗨，任何人都可以帮助我在 laravel Eloquent 中构建以下查询，我真的很困惑在两者之间使用OR条件

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

但不知道如何使用 OR

回答by Bogdan

There is an orWhereBetweenmethod available from the Query Builder, but it is undocumented in the Query Builder Documentation. You can however find it in the Laravel API Documentation.

orWhereBetweenQuery Builder 提供了一种方法，但在Query Builder Documentation 中没有记录。但是，您可以在Laravel API 文档 中找到它。

---

---

The explanations below assume that the variables have the following values:

下面的解释假设变量具有以下值：

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

---

---

Unfortunatelly, using orWhereBetweenfor the second condition is not applicable in your case, because both whereBetweenand orWhereBetweenwill check if a column value is between two input values. This is fine from your first condition since it checks if the existing_startcolumn value is between $newStartand $newEnd. So this is fine:

可惜的是，使用orWhereBetween第二个条件是不适合你的情况，因为两者whereBetween并orWhereBetween会检查是否有列值是两个输入值之间。这对您的第一个条件来说很好，因为它会检查 existing_start列值是否在$newStart和之间$newEnd。所以这很好：

->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 $newStartis between two column values existing_startand existing_end, and there is no Query Builder method that does that. So this will not work:

但是，您的第二个条件想要检查输入值 from$newStart是否在两列值existing_start和之间existing_end，并且没有 Query Builder 方法可以做到这一点。所以这行不通：

->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 1and throw an error.

注意`周围的反引号1，因为 MySQL 将尝试查找名为的列1并抛出错误。

---

---

So the best option here is to use orWhereRawwith bindings like this:

所以这里最好的选择是orWhereRaw与这样的绑定一起使用：

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 $newStartwhich will be properly quoted and escaped to avoid SQL injection.

该?会通过的值来代替$newStart将被正确引用和转义，以避免SQL注入。

---

---

Or course there is always the option of having two grouped conditions that check the boundaries, which would be equivalent to your BETWEENcondition:

或者，当然总是可以选择有两个分组条件来检查边界，这相当于您的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();

Which will compile to:

这将编译为：

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

回答by Alexey Mezenin

You're right, you can use Eloquent's whereBetween(). For OR, you should use orWhere(): https://laravel.com/docs/5.1/queries#advanced-where-clauses

你说得对，你可以使用 Eloquent 的whereBetween(). 对于 OR，您应该使用orWhere()：https: //laravel.com/docs/5.1/queries#advanced-where-clauses

I'm not 100% sure if it'll work, but you can try this:

我不是 100% 确定它是否会起作用，但你可以试试这个：

$data = DB::table('tbl')
        ->whereBetween('existing_start', [$newSTart, $newEnd])
        ->orWhere(function ($query) {
            $query->whereBetween($newStart, [existing_start, existing_end])
        })
        ->get();

回答by Arturs Jerjomins

This work for me.

这对我有用。

    $sql->where(function ($query) {
        $query->whereBetween('start_date', ['2018-04-04', '2018-04-25']);
        $query->orWhereBetween('cancelled_at', ['2018-04-04', '2018-05-01']);
    });

回答by tushar zore

  $start_date_w1 = Carbon::now()->subDays(7)->format('Y-m-d 00:00:00');
  $end_date_w1 = Carbon::now()->subDays(7)->format('Y-m-d 23:59:59');
  $start_date_w2 = Carbon::now()->subDays(14)->format('Y-m-d 00:00:00');
  $end_date_w2 = Carbon::now()->subDays(14)->format('Y-m-d 23:59:59');
  $parents = AdoptiveParent::whereBetween('created_at', [$start_date_w1,$end_date_w1])
              ->orWhere(function ($query) use($start_date_w2,$end_date_w2) {
                  return $query->whereBetween('created_at', [$start_date_w2,$end_date_w2]);
              })
              ->get();
  return $parents;

this query will help you to find parents from (Adaptive Parent Model), which are created before 7 and 14 days. Check for you have used $start_date_w2,$end_date_w2parameter for subquery e.g. ->orWhere(function ($query) use($start_date_w2,$end_date_w2)

此查询将帮助您从(Adaptive Parent Model) 中找到在 7 天和 14 天之前创建的父母。检查您是否已将$start_date_w2,$end_date_w2参数用于子查询，例如->orWhere(function ($query) use($start_date_w2,$end_date_w2)

回答by Jhonny Barrios Sandrea

You have to group the parameters, adapt the example in the documentation

您必须对参数进行分组，调​​整文档中的示例

DB::table('users')
            ->where('name', '=', 'John')
            ->where(function ($query) {
                $query->where('votes', '>', 100)
                      ->orWhere('title', '=', 'Admin');
            })
            ->get();