Laravel公寓租赁项目日期对比查询异常问题

开发公寓租赁项目时，需实现搜索符合「可租日期、城市、客容量、房型」条件的公寓。涉及表：rooms（公寓表）、bookings（预订记录表）、days（可租日期表）、day_room（公寓与可租日期的关联中间表）。

当前问题：在查询的whereExists子句中，使用静态日期（如'2024-06-27'）能正常返回结果，但通过CarbonPeriod遍历日期并调用$date->format('Y-m-d')作为条件时，查询无返回数据——且生成的SQL日期格式看起来与静态日期完全一致。

现有代码 #

namespace App\Http\Controllers;

use App\Models\Room;
use App\Models\Booking;
use Carbon\CarbonPeriod;
use Carbon\Carbon;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;

class SearchController extends Controller
{
    public function index()
    {
        // Validate data later

        $city = request('city');
        $guests_number = request('guests_number');
        $object_type = request('object_type');
        $checkIn = request('checkIn');
        $checkOut = request('checkOut');

        $interval = CarbonPeriod::create($checkIn, $checkOut);

        $rooms = Room::query()
            ->with(['photos', 'bookings', 'days'])
            ->where('rooms.city_id', '=', $city)
            ->where('rooms.beds_num', '>=', $guests_number)
            ->where('rooms.object_type_id', '=', $object_type)
            ->join('bookings', 'bookings.room_id', '=', 
              'rooms.id')
            ->where(function ($query) use ($checkIn, $checkOut) {
                $query->where('checkIn', '>=', $checkOut)
                    ->orWhere('checkOut', '<=', $checkIn);
            })
            ->whereExists(function ($query) use ($interval) {
                $query
                    ->from('day_room')
                    ->join('days', 'days.id', '=', 'day_room.day_id')
                    ->whereColumn('day_room.room_id', 'rooms.id')
                    ->where(function ($query) use ($interval) {
                        foreach ($interval as $date) {
                            $query->where('days.day', '=', $date->format('Y-m-d'));
                            // 静态日期测试时正常
                            // $query->where('days.day', '=', '2024-06-27');
                        }
                    });
            })
            ->get('rooms.*');

        return view('results', ['rooms' => $rooms]);
    }
}

1. 循环叠加AND条件：foreach中多次调用$query->where()会生成AND逻辑，要求days.day同时等于所有遍历的日期——这显然不可能匹配到任何记录；而静态日期仅单个条件，因此能正常返回。
2. 内连接过滤无预订房间：使用join('bookings')会直接过滤掉没有任何预订记录的公寓，导致这部分符合条件的房间被排除。
3. CarbonPeriod范围错误：默认CarbonPeriod::create($checkIn, $checkOut)会包含checkOut当天，但实际业务中checkOut是客人离开日期，该日期不需要可租，应该排除。

1. 调整日期条件：用whereIn替代循环where并验证全匹配 #

收集所有需要的可租日期为数组，通过whereIn批量查询，再通过分组计数确保所有日期都存在于公寓的可租列表中。

2. 改用左连接处理无预订房间 #

使用leftJoin保留无预订记录的公寓，并在条件中处理bookings.id为null的情况。

3. 修正CarbonPeriod的日期范围 #

排除checkOut当天，确保只查询客人实际入住的日期区间。

修改后的代码 #

namespace App\Http\Controllers;

use App\Models\Room;
use App\Models\Booking;
use Carbon\CarbonPeriod;
use Carbon\Carbon;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;

class SearchController extends Controller
{
    public function index()
    {
        // 建议先添加参数验证（如日期格式、checkIn < checkOut等）
        $city = request('city');
        $guests_number = request('guests_number');
        $object_type = request('object_type');
        $checkIn = request('checkIn');
        $checkOut = request('checkOut');

        // 修正日期范围：排除checkOut当天
        $interval = CarbonPeriod::create($checkIn, Carbon::parse($checkOut)->subDay());
        $dates = [];
        foreach ($interval as $date) {
            $dates[] = $date->format('Y-m-d');
        }

        $rooms = Room::query()
            ->with(['photos', 'bookings', 'days'])
            ->where('rooms.city_id', '=', $city)
            ->where('rooms.beds_num', '>=', $guests_number)
            ->where('rooms.object_type_id', '=', $object_type)
            // 改用左连接保留无预订的房间
            ->leftJoin('bookings', 'bookings.room_id', '=', 'rooms.id')
            ->where(function ($query) use ($checkIn, $checkOut) {
                $query->whereNull('bookings.id') // 无预订的房间直接符合条件
                    ->orWhere('bookings.checkIn', '>=', $checkOut) // 预订在入住之后
                    ->orWhere('bookings.checkOut', '<=', $checkIn); // 预订在退房之前
            })
            ->whereExists(function ($query) use ($dates) {
                $query
                    ->from('day_room')
                    ->join('days', 'days.id', '=', 'day_room.day_id')
                    ->whereColumn('day_room.room_id', 'rooms.id')
                    ->whereIn('days.day', $dates) // 匹配所有需要的日期
                    ->groupBy('day_room.room_id')
                    // 确保所有日期都存在于该房间的可租列表中
                    ->havingRaw('COUNT(DISTINCT days.day) = ?', [count($dates)]);
            })
            // 去重，避免因多个预订记录导致重复的房间结果
            ->distinct()
            ->get('rooms.*');

        return view('results', ['rooms' => $rooms]);
    }
}

• 添加distinct()避免因房间有多个预订记录导致重复返回；
• 建议尽早添加参数验证（比如$checkIn和$checkOut的日期格式、$checkIn < $checkOut等），避免无效查询；
• 如果days.day字段是date类型，确保传入的日期格式与数据库存储格式一致（Y-m-d是标准格式，一般无问题）。

内容的提问来源于stack exchange，提问作者Makhmud