Ricardo Isec - 10 months ago 54

SQL 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`

Answer

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();
```

Source (Stackoverflow)