CODEWITHSUNDEEP
mysqllaravellaravel-5eloquent
sanu
sanu

Reputation: 1068

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

Upvotes: 6

Views: 20805

Answers (5)

Jhonny Barrios Sandrea
Jhonny Barrios Sandrea

Reputation: 31

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

Upvotes: 0

Arturs
Arturs

Reputation: 402

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

Upvotes: 2

tushar zore
tushar zore

Reputation: 101

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

Upvotes: 1

Bogdan
Bogdan

Reputation: 44526

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

Upvotes: 12

Alexey Mezenin
Alexey Mezenin

Reputation: 163768

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

Upvotes: 2

Related Questions