Ordering the total from an eloquent model accessor

@tisuchi That won’t work, since the wallet balance is a calculated attribute on the model, not a column in the database.

@tisuchi Thank you, you asked my question to ChatGP*, right? 🤣

@benjamin1509 No.

That shouldn't be the obvious answer?

@kokoshneta Ahh... Sorry! I missed that part!

Thanks for pointing out :)

@tisuchi Ah, sorry, I asked ChatGP*, and gives me the exact same result as your answer.

@Sinnbeck If I use sortBy() it will be $users->sortByDesc('wallet_balance')?

@benjamin1509 Yes, but you have an N+1 issue in your accessor. You should eager load the relationships on the user collection to avoid this.

// Controller
public function users()
{
    $users = User::with(['incomes', 'withdraws'])->where('role', 1)->orderByDesc('created_at')->paginate(20);

    return view('admin.users', [
        'users' => $users,
    ]);
}

// User model
public function getWalletBalanceAttribute(): int
{
    return $this->incomes->sum('amount') - $this->withdraws->sum('amount');
}

Or, if you don’t actually need to load the relationships at all, but only want the sums, you can use the withSum() aggregate function (untested, but something like this):

// Controller
public function users()
{
    $users = User::withSum('incomes', 'amount')->withSum('withdraws', 'amount')->where('role', 1)->orderByDesc('created_at')->paginate(20);

    return view('admin.users', [
        'users' => $users,
    ]);
}

// User model
public function getWalletBalanceAttribute(): int
{
    return $this->incomes_sum_amount - $this->withdraws_sum_amount;
}

@kokoshneta Thanks for letting me know, I'm not sure I can follow the eager loading process. How would you eager load that in the current query?

@kokoshneta Let me try this, one-liner code <3

@benjamin1509 I updated my previous comment to include a version that doesn’t require actually loading the relationships.

@kokoshneta Thank you, I'm trying to apply it, and will update you.

@kokoshneta

I tried your answer along with @sinnbeck 's answer but it didn't order the wallet balance from highest to lowest.

 $users = User::withSum('incomes', 'amount')->withSum('withdraws', 'amount')->where('role', 1)->orderByDesc('created_at')->paginate(20);
$users->sortByDesc('wallet_balance');

@benjamin1509 Hmm… can you show the output of dd($users), at least with one user object expanded? Do the calculated and accessor properties look correct otherwise?

@benjamin1509 Is there any particular reason you used withSum() two times in your last query?

$users = User::withSum('incomes', 'amount')->withSum('withdraws', 'amount')->where('role', 1)->orderByDesc('created_at')->paginate(20); $users->sortByDesc('wallet_balance');

@benjamin1509 If you want pagination, you need to do it in the database. Otherwise you will get 20 that isnt sorted by wallet_balance and then sort them afterwards. I assume you want to sort before paginate

@tisuchi I follow @kokoshneta 's recommendation for that one

@benjamin1509 Just a recommendation. You can merge them together.

$users = User::withSum('incomes', 'amount', 'withdraws', 'amount')
    ->where('role', 1)
    ->orderByDesc('created_at')
    ->paginate(20);

@tisuchi Dont think that would work. It can only take 2 parameters. You can pass an array though

https://laravel.com/api/9.x/Illuminate/Database/Eloquent/Concerns/QueriesRelationships.html#method_withSum

@kokoshneta Sorry for the late response, while I'm trying it and it didn't work, I asked ChatGP* again and used your previous answer as a prompt, and from there I keep asking ChatGP* again from the error that popped out.

The final code that was generated and is working (It even explained it well)😯🎉:

ChatGP*:

I apologize for the oversight. When you retrieve the users, you're sorting the collection, but then converting it back to an Eloquent query builder object, which loses the sort order. To keep the sort order, you can pass the collection to the whereIn method, which will allow you to perform a WHERE IN clause on the id column while keeping the sort order:

public function users()
{
    $users = User::where('role', 1)->get();
    $users = $users->map(function ($user) {
        $user->wallet_balance = $user->wallet_balance;
        return $user;
    });

    $users = $users->sortByDesc('wallet_balance')->values();

    $users = User::whereIn('id', $users->pluck('id'))->orderByRaw(
        'FIELD(id, '. implode(',', $users->pluck('id')->toArray()). ')',
    )->paginate(20);

    return view('admin.users', [
        'users' => $users,
    ]);
}

Before that working answer above, this is the answer provided by ChatGP*, the problem with this is:

The pagination is working but the sorting of wallet balance by descending is not.

public function users()
{
    $users = User::where('role', 1)->get();
    $users = $users->map(function ($user) {
        $user->wallet_balance = $user->wallet_balance;
        return $user;
    });

    $users = $users->sortByDesc('wallet_balance')->values();

    $users = User::whereIn('id', $users->pluck('id'))->paginate(20);

    return view('admin.users', [
        'users' => $users,
    ]);
}

@Sinnbeck Yeah correct, I've encountered that before and checked this exactly the same from the docs.

@Sinnbeck Ahh.. I didn't notice that.

Thanks for sharing the knowledge 😊

@benjamin1509 Ah, I didn’t notice that in the code you’d posted you just did $users->sortByDesc(). That method returns a new collection, so you need to do $users = $users->sortByDesc() instead. I hadn’t paid attention to the pagination either.

The ChatGP* solution will work, but it’s really inefficient – it fetches all users twice in order to get the pagination and the sorting to both work; it generates a long list of specific ID orders to order by in the database (which I suspect will be quite inefficient, though I’m not sure); and it unnecessarily maps over the first collection. It can be done more efficiently by ordering the database result set on the aggregate column names:

$users = User::withSum(['incomes', 'withdraws'], 'amount')
	->where('role', 1)
	->orderByRaw('incomes_sum_amount - withdraws_sum_amount DESC')
->paginate(20);

This should give you a result set that orders by the wallet balance in descending order and then paginates by 20 – you shouldn’t even need the sortByDesc() call, since the correct ordering is now applied directly in the database.

Note: This won’t work in SQL Server (and perhaps some other implementations), though it does work in MySQL. The upshot is that:

• the query is valid according to the ISO/ANSI SQL standard, but
• T-SQL (which SQL Server uses) does not allow referencing columns by their alias in expressions in ORDER BY clauses.

This means ORDER BY incomes_sum_amount - withdraws_sum_amount results in a syntax error (invalid column names) if you’re using SQL Server.

@kokoshneta 👏 how I would do it

@kokoshneta thank you very much for the efforts, this should be the Best answer!