Join 2 tables in a Single Eloquent Laravel using multiple where clause

here I’d like to find the solution to simplify my query to get data using eloquent in Laravel.

$room_id = Booking::whereBetween('from', [$request->from, $request->to])
                     ->orWhereBetween('to', [$request->from, $request->to])
                     ->where('from', '<=', $request->from, )
                     ->where('to', '>=', $request->from)
                     ->pluck('room_id');
                     
$rooms = Room::whereNotIn('id', $room_id )->get();

So here I have 2 Eloquent operations to get Rooms which not included in the Booking Table with specified requirements. So far I have no problem with it, but can you guys give me best practice to simplify from what I do? Thank you.

Answer

Make sure that ‘bookings’ relation is written on your Room model.

$rooms = Room::whereDoesntHave('bookings', use($request) function($q){ 
    $q->whereBetween('from', [$request->from, $request->to])
    $q->orWhereBetween('to', [$request->from, $request->to])
    $q->where('from', '<=', $request->from, )
    $q->where('to', '>=', $request->from)
})->get();