如何用Laravel查询构造器在单查询中获取关联预订集合

要实现你需要的结构（每个房间对象包含对应的reservations数组），单条查询的核心是利用数据库的JSON聚合函数，将每个房间关联的符合条件的预订记录打包成数组字段。以下是具体实现方案：

关键调整说明 #

你的原查询用了嵌套的leftJoin，这会导致预订数据和房间数据平级返回，无法形成嵌套结构。我们需要把预订数据通过聚合函数合并为JSON数组，同时修正关联结构确保customer和wallets正确关联到对应的预订。

方案1：MySQL 8.0+ 版本实现 #

$unitsQuery = DB::table('units as u')
    // 先关联符合条件的预订记录
    ->leftJoin('reservations as r', function ($join) use ($fromDate, $toDate) {
        $join->on('u.id', '=', 'r.unit_id')
            ->where('r.team_id', auth()->user()->current_team_id)
            ->where('r.date_in', '>=', $fromDate)
            ->where('r.date_out', '<=', $toDate)
            ->whereNull('r.checked_out')
            ->whereNull('r.deleted_at')
            ->where('r.date_out', '!=', $fromDate)
            ->where('r.status', '!=', 'canceled');
    })
    // 关联预订对应的客户和钱包数据（如果需要在预订中返回这些字段）
    ->leftJoin('customer as c', 'r.customer_id', '=', 'c.id')
    ->leftJoin('wallets as w', function ($join) {
        $join->on('r.id', '=', 'w.holder_id')
            ->where('w.holder_type', 'App\Reservation');
    })
    ->select([
        'u.id as uid',
        'u.name as uname',
        'u.team_id as utid',
        'u.unit_number as unum',
        'u.status as ustatus',
        'u.sunday_day_price as sunday_price',
        'u.monday_day_price as monday_price',
        'u.tuesday_day_price as tuesday_price',
        'u.wednesday_day_price as wednesday_price',
        'u.thursday_day_price as thursday_price',
        'u.friday_day_price as friday_price',
        'u.saturday_day_price as saturday_price',
        'u.month_price as month_price',
        // 用JSON_ARRAYAGG聚合预订数据为数组，COALESCE确保无预订时返回空数组
        DB::raw('COALESCE(JSON_ARRAYAGG(
            CASE WHEN r.id IS NOT NULL THEN JSON_OBJECT(
                "id", r.id,
                "price", r.price,
                "customer_name", c.name, // 可选：添加客户字段
                "wallet_balance", w.balance // 可选：添加钱包字段
            ) ELSE NULL END
        ), "[]") as reservations')
    ])
    ->where('u.team_id', auth()->user()->current_team_id)
    ->where('u.enabled', 1)
    ->whereNull('u.deleted_at')
    ->orderBy('u.unit_number', 'asc')
    ->groupBy('u.id') // 由于u.id是主键，groupBy它即可（需确保MySQL关闭ONLY_FULL_GROUP_BY或符合其规则）
    ->get();

方案2：PostgreSQL 版本实现 #

PostgreSQL使用JSON_AGG和json_build_object来实现聚合，语法略有不同：

$unitsQuery = DB::table('units as u')
    ->leftJoin('reservations as r', function ($join) use ($fromDate, $toDate) {
        $join->on('u.id', '=', 'r.unit_id')
            ->where('r.team_id', auth()->user()->current_team_id)
            ->where('r.date_in', '>=', $fromDate)
            ->where('r.date_out', '<=', $toDate)
            ->whereNull('r.checked_out')
            ->whereNull('r.deleted_at')
            ->where('r.date_out', '!=', $fromDate)
            ->where('r.status', '!=', 'canceled');
    })
    ->leftJoin('customer as c', 'r.customer_id', '=', 'c.id')
    ->leftJoin('wallets as w', function ($join) {
        $join->on('r.id', '=', 'w.holder_id')
            ->where('w.holder_type', 'App\Reservation');
    })
    ->select([
        'u.id as uid',
        'u.name as uname',
        'u.team_id as utid',
        'u.unit_number as unum',
        'u.status as ustatus',
        'u.sunday_day_price as sunday_price',
        'u.monday_day_price as monday_price',
        'u.tuesday_day_price as tuesday_price',
        'u.wednesday_day_price as wednesday_price',
        'u.thursday_day_price as thursday_price',
        'u.friday_day_price as friday_price',
        'u.saturday_day_price as saturday_price',
        'u.month_price as month_price',
        // PostgreSQL的聚合写法，COALESCE处理空预订场景
        DB::raw('COALESCE(JSON_AGG(
            CASE WHEN r.id IS NOT NULL THEN json_build_object(
                \'id\', r.id,
                \'price\', r.price,
                \'customer_name\', c.name,
                \'wallet_balance\', w.balance
            ) ELSE NULL END
        ), \'[]\'::json) as reservations')
    ])
    ->where('u.team_id', auth()->user()->current_team_id)
    ->where('u.enabled', 1)
    ->whereNull('u.deleted_at')
    ->orderBy('u.unit_number', 'asc')
    ->groupBy('u.id')
    ->get();

核心要点 #

1. 聚合函数的作用：JSON_ARRAYAGG（MySQL）/JSON_AGG（PostgreSQL）会把同一房间的所有预订记录合并成一个JSON数组，正好对应你需要的reservations字段。
2. 空预订处理：用COALESCE将无预订时的null转为空数组[]，保证返回结构的一致性。
3. 关联结构修正：把customer和wallets的关联从嵌套的join闭包中移出，确保它们正确关联到对应的预订记录。

内容的提问来源于stack exchange，提问作者Emad Rashad Muhammed