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

Question

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 this 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?

Can you include the query you are using to get your data? This will help to give some directions on changing it. — Bert H
– Bert H, Commented Jul 5, 2018 at 13:05
The below link has the code i posted on the GitHub page github.com/yajra/laravel-datatables/issues/1790 — Nitish Patra
– Nitish Patra, Commented Jul 6, 2018 at 8:32

Bert H · Accepted Answer · 2018-07-04 08:14:15Z

3

It looks like you're trying to sort by values that aren't columns, but calculated values. So the main issue here is to give Eloquent/MySql the data it needs to provide the sorting.

// You might need to do some joins first
->addSelect(DB::raw('your_calc as outstanding_amount'))
->orderBy('outstanding_amount') // asc can be omitted as this is the default

// Anternative: you don't need the value sorted by
// Don't forget any joins you might need
->orderByRaw('your_calc_for_outstanding_amount ASC')

edited Jul 4, 2018 at 8:14

answered Jul 4, 2018 at 8:04

Bert H

1,1572 gold badges16 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Nitish Patra Over a year ago

Well, all my calculations are done in the editColumn and not in the query() function. So are you suggesting me to write the logic in the query() function itself?

Bert H Over a year ago

After looking at your code, the editColumn function makes data available for Datatables, bu MySql doesn't know about it - so it can't be used for sorting. You'd need to order by a raw SQL statement (so the second option in my answer) Good luck!

Nitish Patra Over a year ago

Sure, i will give it a try.

Nitish Patra Over a year ago

The ->addSelect() works perfectly to ordering each calculated columns. Thanks. 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?

Bert H Over a year ago

Not too sure about it, but I think you'll have to. I'd give it a try first using the previously calculated values to calculate oustanding_amount, otherwise you'll have to do a raw count for it.

Jignasa · Accepted Answer · 2018-10-24 12:23:12Z

0

For SQL functions it'll work as follow

->addSelect(DB::raw('COUNT(products.id) as product_count'));
->orderByRaw(DB::raw('COUNT(products.id)'),'DESC');

edited Oct 24, 2018 at 12:23

answered Oct 24, 2018 at 9:49

Jignasa

1568 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related