Compare sum of sub query column to parent column

I've been banging my head against the wall on this all day (on the verge of literally at this point). I'm terrible at SQL. This query is actually a sub query itself taking place in its parent model scopeQuery.

I've got a charters, trips and a bookings table.

I need to return charters with trips that the sum of all the trip's bookings column bookings.passenger_count does not exceed the trips.capacity column. The relationship is trips->hasMany(Bookings::class);

Variations of this are what I've tried so far and no dice.

// App/Models/Charter 
    public function scopeWithoutFullBookingsOn(Builder $scopeQuery, Carbon $date)
    {
        $scopeQuery->whereDoesntHave('trips', function($query) use ($date) {
            
            return $query
                    ->where('type', 'shared')
                    ->leftJoin('bookings', 'trips.id', '=', 'bookings.trip_id')
                    ->select('*', DB::raw("SUM(passenger_count) as total_passengers"))
                    ->having('capacity', '<=', 'total_passengers');
        });
    }

I also need to narrow it down by the bookings.booking_start_date e.g. ->whereDate('booking_start_date', $date) but I'm trying to solve one thing at a time.

Any ideas?

ChrisArter

5 years ago

Best Answer

Level 1

For future travelers, this is how I got it working:

    public function scopeWithoutFullBookingsOn(Builder $query, Carbon $date)
    {
        $query->whereDoesntHave('trips', function($query) use ($date) {
            return $query
                ->where('type', 'shared')
                ->whereHas('bookings', function($query) use ($date) {
                    return $query
                        ->where('status', 'confirmed')
                        ->whereDate('booking_start_date', $date);
                })
                ->whereRaw("trips.capacity <= ANY (  SELECT SUM( bookings.passenger_count )  FROM bookings WHERE bookings.trip_id = trips.id ) ");
        });
    }

Please or to participate in this conversation.