How to eager load model custom calculated queries

@Thunderson e.g, there is a page that shows users with their debt amount(or watherver that needs calculations or is a model method), and to calculate that you need to for example do ($totalPaidAmount + ...) - $totalOrdersAmount, so when you do that for every user after the user has fetched from database, it causes more than n+1 query.

But that's not too bad for a paginated table, the worse thing is when you want to add a filter that only shows users that are in debt, and I don't even know how to do that when using pagination to display users.

@amir5

Let me share with you my experience how to I resolve N + 1 query problem. Sometimes we need fetch the detail datas for the table and it should cause the N + 1 query performance problem.

Here is basic sample how do I resolve it, if I need get all posts information for the each users.

$users = User::with('posts')->get();

foreach ($users as $user) {
    foreach ($user->posts as $post) {
        echo $post->title;
    }
}

And here is my approach in your case for users and debts table.

$users = User::select('users.*')
    ->selectRaw('
        (SUM(debts.paid_amount) - SUM(debts.order_amount)) AS debt
    ') 
    ->join('debts', 'users.id', '=', 'debts.user_id') 
    ->groupBy('users.id') 
    ->having('debt', '>', 0)
    ->paginate(10); 

// In the view
foreach ($users as $user) {
    echo "User: {$user->name}, Debt: {$user->debt}\n";
}

Please let me know if you have any unclear things or have any additional question.

@amir5 what is your problem exactly, do you resolve N+1 problem or how get filter data with pagination ? let's do it step by step

@Snapey What about those queries that are not relations, like calculations? should I use sub selects/queries?

@Snapey For user balance I've used sub selects, it's a little bit messy, but no n+1 queries, and I easily can filter users by using having.

IFNULL((select sum(share) from `order_items` where order_items.X_id = X.id and exists (select * from `orders` where `order_items`.`order_id` = `orders`.`id`)), 0) - IFNULL((select sum(amount) from `X_paid_salaries` where X_paid_salaries.X_id = X.id),0) as X_balance

@amir5

e.g, there is a page that shows users with their debt amount(or watherver that needs calculations or is a model method), and to calculate that you need to for example do ($totalPaidAmount + ...) - $totalOrdersAmount, so when you do that for every user after the user has fetched from database, it causes more than n+1 query.

But that's not too bad for a paginated table, the worse thing is when you want to add a filter that only shows users that are in debt, and I don't even know how to do that when using pagination to display users.

base on this, you might want to start actually having fields for these kinds of field, so you won't be calculating them in real-time when pulling them.

What you wanna do is list all those fields that needs to be calculated, and create those fields in your users table or let's say another table like users_statistics or whatever that is. Then create an Event:Listener and Jobs for this, every time that certain amount or whatever that you're tracking is related to it is being updated, it will recalculate those and save it on your table.

so the next time you want those data, it's all calculated and ready to be displayed anytime you want with you having to calculate it every time you wanna display it. if those data are nonvolatile, then cached those data as well.