Eloquent Performance: Multiple counting using one single select

If you look at the posts table, you will see that there is a status column which indicates the status of the post:

draft
moderation
published

Say you want to count all the statuses.

The naive approach uses three separate queries:

$draft = Post::where('status', 'draft')->count();
$moderation = Post::where('status', 'moderation')->count();
$published = Post::where('status', 'published')->count();

You can do this in one single query instead:

Post::toBase()->
    ->selectRaw('count(IF(status = 'draft', 1, null)) as draft')
    ->selectRaw('count(IF(status = 'moderation', 1, null)) as moderation')
    ->selectRaw('count(IF(status = 'published', 1, null)) as published')
    ->get();

In the next post, I will discuss the LIKE operator and how it impacts performance.

Summary

Count multiple conditions in one query -- use selectRaw with conditional IF statements to count different statuses (or any grouped values) in a single database round trip instead of issuing one query per condition.

#Summary

Summary