Laravel Many to Many filter with sum

Question

I'm working in laravel and I need to filter the users who orders with certain amount of money in orders so I used to filter the orders count

 User::where('type','client')
            ->has('orders', '>=', $min_orders)
            ->has('orders', '<=', $max_orders)
            ->withCount('orders')
        ->paginate(25)
        ;

Answer 1

If you want SUM with User info then you need to GROUP BY on user info. And If you want the SUM on the Many side table, then you have to JOIN to that table. Assuming you have orders.amount field, you can try this :

<?php

User::select('user.id', 'email', 'name', 'type', \DB::raw('SUM(orders.amount) as amount_sum'))
    ->where('type','client')
    ->join('orders', 'orders.user_id', '=', 'user.id')
    ->has('orders', '>=', $min_orders)
    ->has('orders', '<=', $max_orders)
    ->withCount('orders')
    ->groupBy('user.id', 'email', 'name', 'type')
    ->havingRaw('amount_sum > ?', [50])
    ->orderBy('amount_sum', 'desc')
    ->paginate(25);

This way you can paginate directly into One single query results. But remember that each User column you want to use in SELECT has to be used in GROUP BY too.