Count multiple groups using eloquent in a single query

I recently had to count an arbitrary number of items (from the same table), but having the counts grouped and found a way to do this with Eloquent (and a raw statement). Below is an explanation of how it works and gets setup.

I am wondering if this could be perhaps added as a way to count relations of relations directly in Eloquent. For example:

Model::withCount('relation1' , 'relation1.relation2')

so that the 'relation1' have a property for the relation2_count) -> this is another issue I had. If someone wants to help creating the pull request for this, I am open for this.

So my goal with this is very simple:1) we should avoid using n+1 queries, preferably a single query; 2) we want to use the least amount of memory possible; 3) we want to be fast.

Now the basic setup: we have a bunch of for example hotels, each with rooms available on a per-night basis. So we have models for 'hotels', 'rooms', 'nights' (nights belonging to a room and hotel, and have a 'date' column, a bunch of extra columns to determine if it is available, and most importantly the 'room_id' column, referencing the room). The nights table have unique on ['date', 'room_id'], thus we never have a duplicate entry here. I have also added various useful indices to this table to speed things up.

If we want to check the availability of a single room for a single hotel, for a specific date range (can be single night or even various months), the following query is sufficient (here using a single 'booked' column to determine availability, easy to expand for event more things that cause available/unavailable):

$available = Night::where('hotel_id', $hotel->id)
		->where('room_id', $room_id)
		->where('date', '>=', $check_in_date)
		->where('date', '<', $check_out_date)
		->whereNull('booked')
		->count();

The count at the bottom is simple, I am only interested in how many nights are available for this room in this date range (we have a calculated date difference using Carbon, thus comparing the difference vs the number of nights available tells me it is available or not available).

So for a single room, this is quick and painless, however what happens when we have to check various rooms (each hotel can have 100s of rooms, so by following this to see if we have any available rooms for a given date range, we quickly add more queries). Also the fact that each room have an entry for each day of the year, this table grows extremely fast (on my production of the project, we have over 450 000 rows in this table). In my production, each query took ~300ms, so getting 250 room's availability meant it took 75 seconds (way too slow).

My first alternative was to pull in all the 'night' models for the rooms/hotel selected in the range and then manually filter them in PHP by their 'room_id' to get the counts. The issue here is that we load so many models that we don't actually need (only important thing is the number of nights we it is available), and when the number of rooms is for example 250, and the date range is 30 nights, we get 7 500 models (using way too much memory, and filtering them takes a significant amount of time). Here the query took once again 300ms, but filtering them took ~250ms per room (note that for longer ranges this will escalate quickly) as thus it took 62.8 seconds (still too slow), while using ~80MB memory.

Then I found a solution: using "CASE WHEN" syntax in the query. The basic idea is that MySQL first filters the big table to only relevant results (which I would have received in the first alternative, mentioned above), then it will go through each of them and perform a SUM of the CASE WHEN (using the 'room_id'). This returns a single result with n-number of columns, each column being a SUM/COUNT relevant to this 'room_id' -> thus a single query getting only what I want/need. In my production for 250 rooms, this query took 300ms, used less than 1MB of additional memory, and is just as accurate as the other options.

How I implemented this query is simple:

//first we need an array/collection of room_ids we want to check availability for. It is up to you to determine how you do it
$selects = [];//we are going to build an array of what we will be selecting
foreach($room_ids as $room_id){
		$selects[] = \DB::raw('sum(CASE WHEN `room_id` = '.$room_id.' THEN 1 ELSE 0 END) AS `room_'.$room_id.'`'); //we make use of raw so that it is kept as is (note that room_id should be sanitized/validated).
}
$nights = Night::selectRaw(implode(', ', $selects))  //this is where the magic happens
		->where('hotel_id', $hotel->id)
		->where('date', '>=', $check_in_date)
		->where('date', '<', $check_out_date)
		->whereNull('booked') //the conditions that make it available/unavailable
		->get() //run the query
		->first() //take the first/only result
		->toArray(); //make it an array so that we can easily reference the result with its prefix of 'room_'

The only downside to this generated query is that the Select part becomes very long (depending on the number of rooms in the list) when being viewed, however it runs quick and only returns what I need.

In the case of using it for counts of relations of relations, the returned value can easily be added to the relation's object (like the withCount already does for the main Model), or further summed for the main Model as well.

I though others might also need a simple solution to this issue so I posted it here. Extra suggestions and alternatives are also welcome.