Laravel-datatables: Best way to work with addcolumn sort order

[vanrijs]

Hi,

We are using datatables for years now.
We are still looking for the best way to order on added column data. We have some complex data in the addcolumn function that we can't add to the query. What is the best approach to get a situation we can use the sort order on this type of data. We consider to introduce a new table where we put this complex data and use that table to build the database.
Any other ideas?

For example one of our addcolumn function:

            ->addColumn('request_avg_new', function(\App\User $user) use($product_id) {
                $diff = \Carbon\Carbon::parse($user->cas_histories->where('product', $product_id)->max('created_at'));
                $months = $diff->diffInMonths();
                $num_request = $user->getTotalRequest($product_id, $user->cas_histories->where('product', $product_id)->max('created_at'));
                if($months > 0) {
                    return $num_request / $months;
                } else {
                    return $num_request;
                }
            })

[yajra]

I usually do it on the query level using sub-queries. Something like:

$query = Loan::query()
    ->select('loans.*')
    ->addSelect(
        DB::raw('(select count(*) from receivables where payment_at is not null and loans.id = receivables.loan_id) as payments_count')
    )
    ...