Laravel withCount with conditions in relation attributes

• March 2021
• 1 min read

I have a Coupon model that belongs to Order model and I needed to know how many times this coupon was used for PAID orders.

So I did this...

$coupons = Coupon::where('code', $code)
    ->withCount('orders')
    ->first();

The problem with the snippet above is - you will get the total count of all orders regardless of their attributes but what If I need to apply a condition on the orders relation, say for example only the paid orders needs to be counted?

As it turns out the withCount method can also accept an array of relations and internally it will try to parse whatever constraints you pass to it.

Try this!

$coupons = Coupon::where('code', $code)
    ->withCount([
        'orders as paid_orders_count' => function ($query) { 
            $query->where('status', 'paid');
        },
    ])
    ->first();

Notice the as keyword, this is not database syntax, it's just Laravel's way of knowing what's your desired name of the result column.

The $query argument is just an instance of Illuminate eloquent builder and you can apply any filtering logic you need like where, whereIn, etc...