Natural sorting in datatable

[lorro]

I understand this maybe will not be entirely related to the plugin, but primarily to mysql sorting. Because I use a datatable with server-side processing I can not use datatables js sorting plugins for this.

I have a table with f.e. this data, some records with the following values for its name.

• Apple 0mg
• Apple 12mg
• Apple 3mg
• Apple 6mg

They're also sorted like this, as you can see as a string. Actually I need them to be sorted like 0, 3, 6 ,12.

Do you have any tips/suggestions how I could achieve this? It seems harder than I imagined.

Thanks!

[ypnos-web]

This problem is indeed somewhat unrelated to the plugin, the ORM is doing the ordering. However, you need to tell the plugin that you want to manually order. The easiest way to do this is as follows:

1. Set option "delegateOrder" to true in the $options passed to DataTables->find() in your controller.
2. In your finder (you can write a custom finder and pass its name to the method above), read the customOrder option. Sort the rows accordingly in the finder: To perform the sorting, you can use a lambda that you pass as a reducer in map-reduce.
3. Note that in finder, you can use regular order() calls on the query for all the columns that need no special order (these will then still be sorted by the database). It only gets more complicated if you allow to order by multiple columns. Then your special column may not be responsible for the primary order, so just applying it at the end would not suffice.

There are certainly other methods to do this, but this is probably the most straightforward one to implement.

References: https://book.cakephp.org/3.0/en/orm/retrieving-data-and-resultsets.html#custom-find-methods https://book.cakephp.org/3.0/en/orm/retrieving-data-and-resultsets.html#map-reduce

[lorro]

Correct me if I'm wrong but wouldn't this approach only sort the results within 1 requested page of the datatable results? And not the results over all pages?

[ypnos-web]

You are correct. If you use pagination you will run into this problem.

You can try to do the sorting with custom SQL. So writing a ORDER BY clause that reformats and casts the field, using commands like REPLACE, CONVERT etc. It will also be expensive. I am not aware of any "native" support for natural sorting in SQL.

But this is getting a bit out of scope.

[lorro]

Yes, I understand completely. Thanks for the feedback though ;)