Laravel Eloquent单查询实现场地可用座位筛选方案咨询

Your current code using whereDoesntHave works great for seats with no reservations on the target date, but it misses seats that still have available spots (like a seat with quantity 10 that only has 3 bookings). To fix this, we need to calculate the number of reservations per seat and compare that count directly to the seat's quantity field.

Here's a clean way to do it using a subquery to compute the reservation count:

$seats = SeatCategory::where('place_id', $data['place']['id'])
    ->with(['seats' => function ($query) use ($date) {
        $query->select('seats.*', 
            // Subquery to count reservations for this seat on the given date
            Reservation::selectRaw('count(*)')
                ->whereColumn('reservations.seat_id', 'seats.id')
                ->where('reservations.date', $date)
                ->as('reservations_count')
        )
        // Keep seats where reservations are fewer than total quantity
        ->havingRaw('reservations_count < seats.quantity')
        // Include seats with zero reservations (where the subquery returns null)
        ->orHavingRaw('reservations_count IS NULL');
    }])
    ->get();

Key details here: #

• The subquery inside select calculates how many reservations exist for each seat on your target date, aliasing it as reservations_count.
• We use havingRaw instead of where because we can't reference the alias reservations_count in a where clause in the same query.
• The orHavingRaw('reservations_count IS NULL') ensures seats with no reservations at all are included (since the subquery would return null for those, and null is less than any positive quantity).

If you prefer using a left join instead of a subquery, this version also works:

use Illuminate\Support\Facades\DB;

$seats = SeatCategory::where('place_id', $data['place']['id'])
    ->with(['seats' => function ($query) use ($date) {
        $query->leftJoin('reservations', function ($join) use ($date) {
            $join->on('reservations.seat_id', '=', 'seats.id')
                 ->where('reservations.date', '=', $date);
        })
        ->select('seats.*', DB::raw('count(reservations.id) as reservations_count'))
        ->groupBy('seats.id')
        ->havingRaw('reservations_count < seats.quantity');
    }])
    ->get();

This left join groups seats by their ID, counts related reservations, and filters the same way. Both methods will give you the seats with remaining capacity, grouped under their respective categories.

Just make sure your relationships are properly set up:

• SeatCategory has a hasMany(Seat::class) relationship
• Seat has a hasMany(Reservation::class) relationship

That should solve your problem!

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