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)
;
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.