Eloquent havingRaw problems

Question

I have 2 SQL tables: one that represent tours and the other that represents bookings for those tours. Structure is as follows:

tours
(int) id 
(int) max_participants
(int) state

bookings
(int) id
(fk)  tour
(fk)  date
(int) num_passengers

The bookings table has a date FK but that table is very simple and not important as it is not giving me any trouble.

Now I need to filter my tours and only show to the client the tours that can still accommodate his desired number of passengers. So, assuming a tour has a max_participants of 50 and there are already 48 num_passengers in several bookings for a certain date, I must refuse an additional booking of 3 passengers (as 48+3>=50) but accept an additional booking of 2 passengers (as 48+2>=50).

I came out with this SQL and it works great (some values are hardcoded for readability but they are working with variables):

select * from `tours` where `state` = 1
and exists (select * from `bookings` where `bookings`.`tour` = `tours`.`id` and `date` = 280 having SUM(num_passengers) + 2 <= `tours`.`max_participants`)

When I try to do it in eloquent I got this far:

$tours = Tour::where('state', 1)
->whereHas('toursBookings', function ($query) use ($request, $date) {
    return $query->where('date', $date->id)
        ->havingRaw('SUM(num_passengers) + ' . $request->get('filter_number_passengers') . ' <= ?', [50]);
})
->get();

The works like a charm and does all that it is supposed to do BUT the number 50 is hardcoded! This is a serious problem as each tour has a different number of max_participants.

Can anyone figure out a way to solve this and un-hardcode that 50? It should be tours.max_participants as in the SQL.

Answer 1

If I understood correctly you can do something like this

$max_participants = [50];
$tours = Tour::where('state', 1)
->whereHas('toursBookings', function ($query) use ($request, $date,$max_participants) {
    return $query->where('date', $date->id)
        ->havingRaw('SUM(num_passengers) + ' . $request->get('filter_number_passengers') . ' <= tours.max_participants');
})
->get();