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

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
douzao9845
douzao9845 好的,我知道了。看到我更新的答案(doesntHave+或WhereHas)
一年多之前 回复
dongqigu0429
dongqigu0429 有些房间可能不在预订表中,因为没有预订任何时间......这不是查询,只是我要做的草稿@GordonFreeman
一年多之前 回复
doupo1908
doupo1908 您的查询中的预订是什么意思?
一年多之前 回复

2个回答

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 ;)

dongshai2022
dongshai2022 它显示了所有房间......
一年多之前 回复
$rooms = collect(DB::select("
SELECT * FROM `rooms` LEFT JOIN `bookings` ON `rooms`.`id` = `bookings`.`rooms_id`
WHERE `rooms`.`city` = '$request->city' AND `rooms`.`room_type` = '$request->room_type'
 AND ((`bookings`.`check_in_date` NOT BETWEEN '$check_in' AND '$check_out'
 AND `bookings`.`check_out_date` NOT BETWEEN '$check_in' AND '$check_out')
 OR `rooms`.`id` NOT IN (SELECT `rooms_id` FROM `bookings`))
"));

problem solved using this way, now the question is: can we do it with query builder or its too custom for laravel to build it

Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问