How to server side ORDER BY with datatables.net-editor-server?

YOM

Hi there, I'm using the datatables.net-editor-server and am perplexed on how to add an order by clause to the Editor instance. It doesn't seem to be possible in the docs but I have a hard time thinking that wouldn't be a feature.

Is there a way to do this using the library, or some sort of work around? I'm not interested in a front end based solution- I'm building out a way to add custom sorts to certain columns for this use case and future use cases and would prefer to keep that logic on the server side.

I of course have access to the column being ordered and the order direction but I'm stuck on how to add my own custom ORDER BY clauses to the Editor

allan

Hi,

Are you using client-side or server-side processing? If client-side, then server-side order is irrelevant - the sorting will be done by the client-side Javascript.

If server-side processing, that the ORDER BY statement will be added to the SQL queries automatically based on the ordering applied to the table.

Allan

YOM

Hey Allan, I need to alter the ORDER BY statement depending on which column is used (and if it has an existing search value) for my use case. Is there a way to directly change the ORDER BY?

And yes I'm using server side processing

allan

If you are using server-side processing, the column the table is being sorted on will dictate the SQL order statement and the library will apply that.

What is the change you need to make? If you need multi-column sorting you can use this option.

There is no direct way to set the ORDER BY statement, since it is handled automatically.

Allan

allan

This is the code that does the automatic application of the ordering for server-side processing.

Allan

YOM

Ah that's unfortunate. Seems I may have to modify the library a bit then to make this happen.

I need to sort by one of two underlying columns depending on what the user has searched inside the column. The use case is a little too complicated to explain in specific detail but it's essentially a case of a "display" focused column that shows a variety of data (some including error messages, some numerical figures, etc), but the underlying data type is a string, and therefore strings containing number data aren't sorted properly.

I need to sort by one of two numerical columns to have the sort function working correctly when the user is searching for specific statuses on that column. This would be trivial to do if I could directly alter the ORDER BY statement, but that's alright. I haven't had to modify NPM libraries much so this could be an interesting experience. Or incredibly painful, we'll see

YOM

Actually, it seems I could just manually alter the order array in the event body as needed to change the column index value to the correct one, and then datatables would just automatically sort by the correct column. Not sure why that just dawned on me, but maybe that's helpful for another user with this issue. Phew

allan

Sounds good. Let me know if that works okay for you. If you find you still need access to the order method, there is a cheeky little hack - use the where() method with a function. The parameter passed in is the query - you can add the orderBy to that. I'm not sure I've ever tried it in the Node.js libs, but I've used it in the PHP ones for a few tweaks.

Allan