Logical Grouping in Laravel Query Builder

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:

...

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:

use App\Models\Reservation;

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

    return $isAvailble;
}

Then, we fill it with intersection check logic:

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:

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;