Count and Groupby a value of a nested relationship in Laravel Eloquent-CodePudding

I have 3 models that are as follows

Orders
|id, customer_id, created_at, updated_at|

Customers
|id, name, zip_code, gender, created_at, updated_at|

Genders
|id, name, created_at, updated_at|

I am working on some sort of statistical dashboard. What I would like to do is to basically group the customers based on their gender so that I can have an output as below

Male = 60, Female = 65 etc.

The Gender table basically contains records for each gender whilst the gender column on the customers table contains the gender id from which the gender name can be determined. I have set up all the necessary relationships in the Model and I am able to run the code below which produces me a list with the genders however, I am struggling to group and then count how many customers are which.

            $orders = Order::with('customer.gender')
                ->get()
                ->groupBy(['gender.name']);

CodePudding user response：

Try this:

$orders = Order::with('customer.gender')
                ->select(DB::raw('count(*) as total'))
                ->get()
                ->groupBy(['gender.name']);

CodePudding user response：

You could do it with a couple of joins.

Gender::query()
    ->selectRaw('count(*) as count', 'genders.name')
    ->join('customers as c', 'genders.id', 'c.gender')
    ->join('orders as o', 'o.customer', 'c.id')
    ->groupBy('genders.name')
    ->get();

The model you start from (in this case Gender) doesn't really matter. Just that you join the other two tables.

Alternatively, with your approach maybe this would work:

Gender::query()
    ->with(['customers' => fn($customer) => $customer->withCount('orders')])
    ->get()
    ->mapWithKeys(fn($gender) => [
        $gender->name => $gender->customers->sum('orders_count')
    ]);