Laravel Livewire sorting non sql data

Question

Hi im wondering if it possible to sort a field which is not field of sql table is a balance of two fields. I have a table with fields that sorting works but when i try to sort that field which isn't field of sql table getting error that make sense,

  <th class=" tracking-wider"wire:click="sortBy('balance')" style="cursor: pointer;">Balance</th>
   <td>{{ ((int)$charge->taskscharges - (int)$charge->payment)}}&euro; </td>

The livewire component :

   public function sortBy($field){
       if ($this->sortDirection =='asc'){
           $this ->sortDirection ='desc';

       }
       else{
           $this->sortDirection ='asc';
       }
       return $this ->sortBy = $field;
   }

   public function render()
   {   $charges = charge::query()
       ->search($this->search)
       ->orderBy($this->sortBy,$this->sortDirection)
       ->paginate($this->perPage);

       return view('livewire.charges.show',['charges'=>$charges
       ]);
   }

Answer 1

Would this works for you ?

$charges = charge::query()
       ->selectRaw('*, taskscharges - payment as balance')
       ->search($this->search)
       ->orderBy($this->sortBy, $this->sortDirection)
       ->paginate($this->perPage);

 <th class=" tracking-wider"wire:click="sortBy('balance')" style="cursor: pointer;">Balance</th>
 <td>{{ ((int)$charge->balance)}}&euro; </td>

Since you're paginating, I think it's better to compute the result before fetching the paginated charges from the DB.

You could do after paginating using sortBy(function($charge, key){...}) But you will have unexpected results as a balance could be higher on the second page.