Disable sorting in Editor options field (PHP)

maxmedia

Description of problem: I have source of Editor field options made as database view with elaborated sorting. Is there a way to keep db sorting in field options? PHP API docs for order() method states "If this option is not provided the ordering will be based on the rendered output, either numerically or alphabetically based on the data returned by the renderer." While I use field renderer, output get sorted anyway (wrong way).
I was also trying to directly use order() method, but my sort rules seems to be too much. In case you are curious, I need someting like this: order by date is null desc, name, date desc

allan

On the client-side you would set columns.orderable to false.

There isn't currently an option for the server-side Field class to tell it not to be orderable. I mean, you could tweak the code to do that, but normally you would just disable it on the client-side.

Allan

maxmedia

Maybe I didn't made it clear: my concern is not about sorting column in datatable, but sorting of data source for Editor dropdown field options. It's ok if corresponding datatable column can be sorted in any way.
I just need the dropdown to show options in an order returned by database. I can't use order() method https://editor.datatables.net/docs/2.3.2/php/classes/DataTables-Editor-Options.html#method_order, seems it can't handle such complicated ORDER BY clause. But I need to use render() method, which sorts db output where it shouldn't.

allan

The Options class will do an alphabetic sort on the data retrieved from the database if there is no order clause given to it. The code for that is here.

Unfortunately you are right though, the order statement can't currently handle something quite like that - it will try to escape parts of the string that it shouldn't.

One workaround would be to create a VIEW with the query (and order) that you want, and then get the options from the VIEW, including setting the order parameter.

Allan

maxmedia

Well, this is how I already tried to solve this: I made a VIEW with correct order I want, from which I get the options. To use order parameter I would need column that just keep my order, like number increasing for each row. Depending on underlying db engine that might be possible, e.g. https://mariadb.com/kb/en/row_number/
But I guess this comes with big performance penalty, considering that results are already sorted. So the best way would be to tell Options class not to sort data retrieved from database.

allan

Unless there are a very large number of options, I doubt the performance will be significantly impacted. I do take the point about having a way to disable the auto order without specifying an SQL column to order on. Perhaps passing false to the Options->order() method. I've added that to my todo list!

Allan