Laravel-datatables: Sorting Issue After Table Render in Laravel DataTables as a Service Implementation

[NitishKumarPatra]

Unable to Sort by columns that are calculated columns after table render

I have implemented laravel dataTable as a service. The initial two columns are actual id and names so, I am able to sort it asc/desc after the table renders. But the next few columns renders after performing few calculations, i.e. these values are not fetched directly from any column rather it is processed. I am unable to sort these columns where calculations were performed, and I get error. And I know it is looking for that particular column for eg outstanding_amount which I don't have in the DB, rather it is a calculated amount from two or more columns that are in some other tables. Any Suggestions on how to overcome this issue?
@yajra

My Calculations are done in the ->editColumn space.

    protected $totalPayments = 0;
    protected $totalWallet = 0;
    protected $outstandingAmount = 0;

function __construct(Datatables $datatables, Factory $viewFactory)
{
    $this->totalPayments = 0;
    $this->totalWallet = 0;
    $this->outstandingAmount = 0;

    parent::__construct($datatables, $viewFactory);   
}
public function 
{
  return $this->datatables
        ->eloquent($this->query())
        ->editColumn('users.user_id', function($user){
            return $user->user_id;
        })
        ->editColumn('users.first_name', function($user){
            return $user->full_name;
        })
        ->editColumn('payment_amount',function($user){
            $this->totalPayments = 0;
            $payments = Payment::select('total_amount')->whereUserId($user->id)->whereStatus('Success');
            if(count($payments)){
                if($payments->sum('total_amount') > 0){
                    $this->totalPayments += floatVal($payments->sum('total_amount'));
                }
            }

            return $this->totalPayments;
        })
        ->editColumn('wallet_amount',function($user){
            $this->totalWallet = 0;
            $wallet = Wallet::select('wallet_amount')->whereUserId($user->id)->whereStatus('Success');
            if(count($wallet)){
                if($wallet->sum('wallet_amount') > 0){
                    $this->totalWallet += floatVal($wallet->sum('wallet_amount'));
                }
            }
         return $this->totalPayments;
        })
        ->editColumn('outstanding_amount',function($user){
            $this->outstandingAmount = 0;
            $this->outstandingAmount = $this->totalPayments - $this->totalWallet;
            return $this->outstandingAmount;
        });
   }
}

public function query()
{
    $query = User::withoutGlobalScopes()->whereNotNull('users.user_id')->select('users.*');
    return $this->applyScopes($query);
}

[yajra]

Unfortunately, sorting of computed columns when using query/eloquent builder is not possible. You would have to do the computation on the SQL Level to be able to sort it.

Another way is to use collection, but it has downside where performance will be slow if you have bulk data.

[NitishKumarPatra]

Ok thank you, I will try with the query. Probably I won't be using collections, as that might wreck up the speed.

[NitishKumarPatra]

I used the ->addSelect('logic_for calculation as column_name') on the query and it works perfectly to order each calculated columns. Thanks, @yajra . The only doubt I have now is, if u see my code, the last column is the outstanding amount, which is neither a column nor any calculation logic occurs. It just adds up the previously calculated column values. Now how should I accommodate the code for outstanding in the addSelect()? Do I have to write the raw queries for all the above addSelect into the last addSelect?