Laravel data computation, sorting, and pagination: How to make it reusable and effortless?

Hello everyone,

I'm currently working on my first relatively complex Laravel application and could use some guidance. I need to display data on the frontend that involves both computed values and the ability to sort based on user input, all while maintaining pagination.

For instance, I've created an accessor in my Player model as shown below:

    public function getRewardReceivableAttribute()
    {
        return $this->rewards()
            ->where('status', RewardStatus::CONFIRMED)
            ->sum('value');
    }

However, I can't use this accessor along with ->orderBy('reward_receivable').

I'm aware that I can use sortBy(...) on a collection, but this approach causes issues with pagination.

To address this, I tried using the following query (a simplified example) that utilizes joins and selects with DB::raw to allow for sorting:

return Player->join('positions', 'players.position_id', '=', 'positions.id')
    ->join('teams', 'players.team_id', '=', 'teams.id')
    ->select(
        'players.id as id',
        'first_name',
        'last_name',
        'icon',
        'positions.name_cs as position',
        'last_price',
        'teams.name as team_name',
        DB::raw('(SELECT SUM(value) FROM rewards WHERE player_id = players.id AND status = 1) as reward_receivable')
    )->orderBy($this->sortField, $this->sortDirection)
->paginate(15);

My challenge is that this approach isn't very reusable. For instance, I might need to use reward_receivable in multiple parts of my application, and currently, I'm forced to calculate it within DB::raw. I'd prefer to create some sort of accessor for better maintainability while still being able to use ->orderBy(...) for sorting and pagination.

I have a feeling that I'm missing a crucial aspect here. Can someone provide guidance or suggest a better approach?

Thank you in advance for your assistance!