Laravel-datatables: Cannot sort/order addColumn that is COUNT or SUM

[coryrose1]

Summary of problem or feature request

I am using Eloquent as a data source and server-side processing.

I'm essentially pulling the User model into the datatable, but there are a few additional columns.

One column is a COUNT of a relation (i.e. Users have Sales, I'm returning a count of sales):

addColumn('num_sales', function($row){
                return $row->sales()->count();
            })

Another column is a SUM of Sales's 'amount' column:

addColumn('total_amount', function($row){
                return $row->sales()->sum('amount');
            })

The datatable is rendering fine and the JSON response is like this:

      {  
         "id":"1",
         "first_name":"ABC",
         "last_name":"123",
         "email":"[email protected]",
         "agreed_terms":0,
         "name":"ABC 123",
         "num_sales":"2",
         "total_amount":"0",
      },

All columns sort other than num_sales and total_amount. Are you able to identify why the COUNT or SUM columns will not sort?

I'm on Laravel 5.4. Please let me know if I can offer any additional information.

[yajra]

For count, you can use Laravel's withCount('relation'). However, you need to manually add the SQL for SUM since it's not yet supported by Laravel.

[lk77]

i'm using sql count and it's working well :

\DB::raw('count(distinct client_id) as nb_client')

you will perhaps need a join with the sale table

[realcheese]

For count, you can use Laravel's withCount('relation'). However, you need to manually add the SQL for SUM since it's not yet supported by Laravel.

Can you give an example of the SQL needed to sort columns that have been added? These added columns are sums of the relation. I've been stuck on this for awhile.

[yajra]

@realcheese it depends on your data. One quick example

$data = DB::table("click")
        ->select(DB::raw("SUM(numberofclick) as count"))
        ->orderBy("created_at")
        ->groupBy(DB::raw("year(created_at)"))

If you are counting / sum from relations, you then need to do some join statements. The key here is as long as it works on the SQL level, it should work with dataTables. There are cases that it is better to use join statements than relying on eager loading. But all depends on the situation. Thanks!

[realcheese]

@yajra The join statements definitely did the trick. I was so intent on using eager loading that I didn't use the query builder. Thanks for the help!