Logical Grouping in Laravel Query Builder
Tags: laravel, sql, php, mysql
Published: 2022-03-15 15:52:54

When you could work well with SQL query, sometimes when it come to laravel query builder (or any framework query builder) there are  problems on to how to implement a certain complex SQL query to the query builder. One of the common problem is the logical grouping, or we could say the parenthesis in the conditional statement. Here is how we could write in Laravel Query Builder or Eloquent, with an example.

## Requirements

You know how to work with basic Laravel Query Builder and processing the parameters from request.

## The Case

I will demonstrate how to check an availability of a ranged date on reservations list. It will check whether the requested `start_date` and `end_date` are not intersecting any reservation.

Let's say we have an instance called `Reservation`. It simply has two important attributes, `checkin_date` and `checkout_date`:

```
Reservation
  |_ id
  |_ checkin_date
  |_ checkout_date
```

You could create the `Model` and `migrations` for it.
And then, there is a request containing `start_date` and `end_date` parameters.

## Logical Grouping Example

The query builder logic will take a place in a controller method and you could create it first with any name you want to follow along. it accepts a laravel `Request` instance:

```php
...

use Illuminate\Http\Request;

...

public function index(Request $request)
{
  //
}

...
```
 
We start with opening a Reservation query builder and the result will tell whether it's available or not based on the requested `start_date` and `end_date`:

```php

use App\Models\Reservation;

public function index(Request $request)
{
    $isAvailable = Reservation::query()
      ->doesntExist();

    return $isAvailble;
}

```

Then, we fill it with intersection check logic:

```php

public function index(Request $request)
{
    $isAvailable = Reservation::query()
        ->where(function ($query) use ($request){
               // check for the requested dates are intersecting a 
 reservation checkin_date
               $query->where('checkin_date', '>=', $request->start_date)
                     ->where('checkin_date', '<=', $request->end_date);
           })
        ->orWhere(function ($query) use ($request){
               // check for the requested dates are intersecting a reservation checkout_date
               $query->where('checkout_date', '>=', $request->start_date)
                     ->where('checkout_date', '<=', $request->end_date);
           })
        ->orWhere(function ($query) use ($request){
               // check for the requested dates is within a reservation range
               $query->where('checkin_date', '<=', $request->start_date)
                     ->where('checkout_date', '>=', $request->end_date);
           })
        ->orWhere(function ($query) use ($request){
               // check for a reservation is within the requested dates
               $query->where('checkin_date', '>=', $request->start_date)
                     ->where('checkout_date', '<=', $request->end_date);
           })
        ->doesntExist();

    return $isAvailable;
}

```

the above query builder logic has similar functionality with this query:


```sql

SELECT COUNT(*)
FROM reservations
WHERE (checkin_date >= '{start_date}' AND checkin_date <= '{end_date}')
OR (checkout_date >= '{start_date}' AND checkout_date <= '{end_date}')
OR (checkin_date <= '{start_date}' AND checkout_date >= '{end_date}')
OR (checkin_date >= '{start_date}' AND checkout_date <= '{end_date}')
LIMIT 1;

```