How to group on multiple fields including nested eager columns-CodePudding

I have some carts, products and users. I try to get statistics like number of carts per user. And here are my models:

user.php

class User {
    public function products()
    {
        return $this->hasMany(Product::class, 'user_id');
    }
}

cart.php

class Cart {

    public function products()
    {
       return $this->hasMany(Product::class, 'cart_id');
    }     
}

product.php

class Product {

    public function user()
    {
        return $this->belongsTo('User', 'user_id');
    }

    public function cart()
    {
        return $this->belongsTo(Cart::class, 'cart_id');
    }      
}

Here is my query:

$query = Cart::query()
        ->whereHas('products', function ($query) use ($filter) {
            $query->where('whatever', $filter);
        })
        ->join('product', 'product.cart_id', '=', 'cart.id')
        ->join('users', 'users.id', '=', 'product.user_id')
        ->groupBy('users.id')
        ->select('users.id as user_id')
        ->select('users.name')
        ->selectRaw('count(cart.id) as numberOfCarts')
        ->selectRaw('SUM(price)')
        ->orderBy('users.name')
        ->get();

What I get is number of products per user, but I want number of carts per user. when I try to group using cart.id like groupBy(['user.id', 'cart.id']), it is worse: I get several times the same user and at each time the number of products in cart. And this total gives the same as total of number of carts get previously.

I have added jointure because I don't make it work by trying grouping on nested eager relationship. So I make it simplier.

How to fix grouping to count users carts and not their products ?

CodePudding user response：

The issue is that count() counts the number of records returned by your MySQL query, so if you're looking at 20 rows all with the same cart.id, it's still going to count all 20 of them.

You can reduce this to what you want by only counting unique cart.ids with distinct:

$query = Cart::query()
        ->whereHas('products', function ($query) use ($filter) {
            $query->where('whatever', $filter);
        })
        ->join('product', 'product.cart_id', '=', 'cart.id')
        ->join('users', 'users.id', '=', 'product.user_id')
        ->groupBy('users.id')
        ->select('users.id as user_id')
        ->select('users.name')
        ->selectRaw('count(distinct cart.id) as numberOfCarts') // << Changed line
        ->selectRaw('SUM(price)')
        ->orderBy('users.name')
        ->get();