Laravel 5 using OR condition with BETWEEN

问题:

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

回答1:

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

回答2:

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

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

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

回答3:

  $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_w2 parameter for subquery e.g. ->orWhere(function ($query) use($start_date_w2,$end_date_w2)