groupBy within withCount closure (Laravel 5.3.26)

Question

Within Laravel 5.3.26, is it possible to group the result of withCount based on a column of that relationship? Something as follows, for example:

$brands = \App\Models\Brand::withCount([ 'products' => function ($query) {
        $query->groupBy('extra_attribute');
    } ])
    ->get();

This particular piece of code returns an empty collection.

Maybe I'm just not looking in the right places in the documentation or not googling properly, but I can't find anything about this particular scenario.

Could somebody point me into the right direction?

Thanks a bunch!

---

Edit: Currently I appear to have solved it with this:

$brands = \App\Models\Brand::with([ 'products' => function ($query) {
    $query->select('brand_id', 'extra_attribute')
        ->selectRaw('COUNT(*) AS products_count')
        ->groupBy('extra_attribute');
} ]);

and being able to fetch the total through something like $brand->products->sum('products_count').

Feels extremely dirty, and probably is. Is this really the only way?

Answer 1

The groupBy() shouldn't be used in withCount() as withCount() is going to eager load the count of all items in the collection of products for each Brand grouped by Brand in the returned collection. This would be equivalent to $brand->products->count() on a single Brand.

Between your 2 examples, the groupBy() is unnecessary if you aren't using this data elsewhere. If you are, it may be better to access your totals in the collections as:

//Controller
$brands = \App\Models\Brand::with('products')->get();

//View
@foreach($brands as $brand)
    <div>TOTAL PRODUCTS: {{ $brand->products->count() }}</div>
    @foreach($brand->products->groupBy('extra_attribute') as $attribute => $products)
        <div>TOTAL {{ $attribute }}: {{ $products->count() }}</div>
    @endforeach
@endforeach

--OR--

You can have the best of both worlds and attach the count and the grouped relation by just doing both:

$brands = \App\Models\Brand::withCount('products')
    ->with(['products' => function ($query) {
        return $query->groupBy('extra_attribute');
    }])->get();

Then you can access the brand's products_count and grouped products:

$brands->each(function($brand) {
    $brand->products_count;
    $brand->products->each(function($products, $extra_attribute) {
        // Grouped Products
    });
});

Answer 2

Of course, you can pass a closure for withCount() too just like you do in with(), e.g as follows which gives visit count of user_id 1 on product_id 1

User::where('id', 1)
->withCount(['visits'=> function($q) use ($product_id){
   $q->where('product_id', $product_id);
}])