How to get multiple total counts in a single query with different condition in Laravel

Suppose we have an “Orders” table with three different status of the order like Cancelled, Accepted, Pending etc. If we want to count the total of each type of order, there may be different possibilities, but let’s find out the most efficient one. Otherwise, if data is huge, our query will not be executed or will take a huge time. Let’s assume we have a Flag field for the status with a possible value of [1, 2, 3] Cancelled, Accepted, and Pending orders respectively.

The first possibility comes to our mind is to run the query for each status with count function like:

$totalOrders = Orders::count();
$cancelledOrders = Orders::where(‘Flag’ 1)->count();

And so on for the rest of the conditions.

 

The above approach is the simplest one and we can achieve the desired output. Although it’s simple and easy but not an efficient way to deal with conditions like this.  We have to execute four queries (in the above case) just for the sake of counting. If conditions increase the no of queries will also increase. Let’s try another approach.

$orders = Orders::all();
$totalOrders = $orders->count();
$cancelledOrders = $orders->where(Flag, 1)->count();

The same goes for the others conditions as well.

Although we run a single database query here, trust me, this is not a good idea, tons of memory will be used and also will take a significant amount of time to process the data, especially if you are dealing with large data please this is totally not a recommended way. This approach is even worst than the first one.
 

Thanks to the aggregate functions of SQL, these make our life much easier. We can use a simple and single query to get the desired work done in a much efficient way.

 

Let’s use the conditions with the aggregate functions in the following manner:

$countOrders = DB::table('orders')
     ->selectRaw(“count(*) as totalOrders”)
     ->selectRaw(" count(case when Flag = 1 then 1 end) as cancelledOrders")
     ->selectRaw(" count(case when Flag = 2 then 1 end) as acceptedOrders")
     ->selectRaw(" count(case when Flag = 3 then 1 end) as pendingOrders")
     ->first();

This single query will return the counts of all the conditions, also we can add more conditions to the above query easily. This is an efficient way to get the counts of multiple statuses in a single query.

 

Now we can print each counts in our Laravel blade file as:
{{ acceptedOrders }}, {{ cancelledOrders }}, {{ pendingOrders }}.