Returning courses where the sum of it's bookings quantity attribute is less than the event attendee limit

I have a Course model that has an attendee_limit and has a hasMany relation to bookings.

Each booking has a quantityattribute - which stores the number of attendees.

I'm looking to build up a query that gets Courses that have available spaces.

Closest i've got is this... but it only returns Courses that have bookings, im think it's because of the join.....

$courses = Course::with('bookings')
            ->join('bookings', 'bookings.bookable_id', '=', 'course.id')
            ->selectRaw('(courses.attendee_limit - sum(bookings.quantity)) as remaining_spaces')
            ->groupBy('courses.id')
            ->having('remaining_spaces', '>', 0)
            ->get();

And, there needs to be a where clause in there somewhere that only counts the quantity of bookings that have a status of paid. :/

Nakov

5 years ago

Level 73

@neilcarpenter give this a try:

Course::with('bookings')
    ->withCount(['bookings' => function($query) { 
        $query->select(DB::raw('SUM(bookings.quantity)')); 
    }])
    ->whereHas('bookings', function($query) { 
        $query->where('bookings.status', 'paid');
    })
    ->havingRaw('bookings_count > courses.attendee_limit')
    ->get()

To get the difference on how many are left, you can then do, bookings_count - attendee_limit.

Please or to participate in this conversation.