douou2026
2019-05-11 18:44 阅读 38

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

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
  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享

2条回答 默认 最新

  • doushanmo7024 doushanmo7024 2019-05-11 19:58

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

    点赞 评论 复制链接分享
  • dpepbjp126917 dpepbjp126917 2019-05-12 11:07
    $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

    点赞 评论 复制链接分享

相关推荐