Covert SQL to Laravel Query Builder-CodePudding

I'm a beginner regarding Laravel Query Builder, can you guys help me convert this SQL to Query Builder

public function check_available_room(Request $request, $checkin_date)
{
    $available_rooms = DB::SELECT("SELECT * FROM rooms WHERE id NOT IN (SELECT room_id FROM bookings WHERE '$checkin_date' BETWEEN checkin_date AND checkout_date)");
    
}

Book model

function rooms() {
   return $this->belongsTo(Room::class, 'room_id');
}

Room model

public function bookings() {
    return $this->hasMany(Booking::class);
}

Thank you so much

CodePudding user response：

Here is a solution if you have your models and relations in place

$availableRooms = Room::whereDoesntHave('bookings', function($bookingQuery) use ($checkin_date) {
    $bookingQuery->where('checkin_date', '<', $checkin_date)
    ->where('checkout_date', '>=', $checkin_date);
})->get();

CodePudding user response：

$unavailable_room_ids = Booking::where('checkin_date', '<', $checkin_date)
    ->where('checkout_date', '>=', $checkin_date)
    ->pluck('room_id')->get();

$available_rooms = Room::whereNotIn('id', $unavailable_room_ids)->get();