Development Blog
Collection of development articles I've put together...
Eloquent - Calculating totals using conditional aggregates
How to correctly calculate totals in Eloquent using conditional aggregates
4
Likes
Stuart Todd
•
2 years ago
•
Laravel
The requirement
Imagine you've got a page which shows a list of features, there's a new requirement where we need to display several totals based on the current status of the feature.
Something which looks like this:
If we add the totals, the appearance would change to something like this:
But what's the most efficient way of calculating the totals?
Option 1 - Running separate queries
$statuses->requested = Feature::where('status', 'requested')->count();
$statuses->planned = Feature::where('status', 'planned')->count();
$statuses->completed = Feature::where('status', 'completed')->count();
This approach is simple enough and for this example, it's not a big deal as we've only got 3 statuses, but what if we had 5, or 10, or 20?
That's when this approach starts to become a problem!
Option 2 - A single database query
It is possible to fetch everything you need in one database query by writing count aggregate queries. The query would look like this:
SELECT
count(CASE WHEN status = 'requested' THEN 1 END) AS requested,
count(CASE WHEN status = 'planned' THEN 1 END) AS planned,
count(CASE WHEN status = 'completed' THEN 1 END) AS completed
from features;
So how do we do this with Eloquent?
$statuses = Feature::toBase()
->selectRaw("count(CASE WHEN status = 'requested' THEN 1 END) AS requested")
->selectRaw("count(CASE WHEN status = 'planned' THEN 1 END) AS planned")
->selectRaw("count(CASE WHEN status = 'completed' THEN 1 END) AS completed")
->first();
We've now executed one query (so we've got a scalable solution / no N+1 problem), not three and this query is even quicker than the first one ran above.
