如何编写正确的查询构建？ 我被卡住了

Question

So I need a way to search in rooms with some criteria, this comes from a form in home page where user search by city, room_type, and check_in_date, check_out_date with datepicker I want to bring the rooms that are in rooms WHERE (input.check_in_date NOT BETWEEN (bookings.check_in_date AND bookings.check_out_date) AND input.check_in_date NOT BETWEEN (bookings.check_in_date AND bookings.check_out_date) OR NOT IN bookings) WHERE rooms.city = input.city AND rooms.room_type = input.room_type

I tried a lot of time with queries but i am complete stuck here, I find it a lot easier with laravel query builder at the moment.

    $check_dates = explode(' - ', $request->datetimes);
    $check_in = date('Y-m-d', strtotime($check_dates[0]));
    $check_out = date('Y-m-d', strtotime($check_dates[1]));
    $room_types = RoomTypes::all();
    $rooms = DB::table('rooms')
        ->leftJoin('bookings', function ($join) use ($check_in, $check_out) {
            $join->on('rooms.id', '=', 'bookings.rooms_id')
                ->where(function ($q) use ($check_in, $check_out) {
                    $q->whereNotBetween('bookings.check_in_date', [$check_in, $check_out])
                        ->whereNotBetween('bookings.check_out_date', [$check_in, $check_out]);
                })->orWhere(function ($q) {
                    $q->whereNotIn('rooms.id', DB::table('bookings')->select('rooms_id'));
                });
        })
        ->where('rooms.city', $request->city)
        ->where('rooms.room_type', $request->room_type)
        ->get();

the query is this

    SELECT * FROM `rooms` LEFT JOIN `bookings` ON `rooms`.`id` = `bookings`.`rooms_id`
 WHERE ((`bookings`.`check_in_date` NOT BETWEEN $check_in_date AND $check_out_date
 AND `bookings`.`check_out_date` NOT BETWEEN $check_in_date AND $check_out_date)
 OR `rooms`.`id` NOT IN (SELECT `rooms_id` FROM `bookings`)) AND `rooms`.`city` = $city AND `rooms`.`room_type` = $room_type

Answer 1

I would use the Eloquent whereHas method. It would look like this:

$rooms = Room::doesntHave('bookings')->orWhereHas('bookings', function ($query) use ($check_in, $check_out) {
    $query->whereNotBetween('check_in_date', [$check_in, $check_out])
          ->whereNotBetween('check_out_date', [$check_in, $check_out]);
})
->where('city', $request->city)
->where('room_type', $request->room_type)
->get();

This code is not tested. Just an idea how it should be easier to solve ;)