SQL Error with Datatables JS BIN environment

SQL Error with Datatables JS BIN environment

kthorngrenkthorngren Posts: 20,591Questions: 26Answers: 4,823

If orderable is set false on the first column with server side processing the following error occurs:

DataTables warning: table id=example - An SQL error occurred: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'LIMIT 0, 10' at line 5

Here is the test case:



  • colincolin Posts: 15,208Questions: 1Answers: 2,592

    This thread should help, it's asking the same thing. And here's your test case updated with the recommendation.



  • kthorngrenkthorngren Posts: 20,591Questions: 26Answers: 4,823
    edited November 2020

    I don't use server side processing and never paid attention to the columns[i][orderable] parameter. The docs do state this:

    Flag to indicate if this column is orderable (true) or not (false). This is controlled by columns.orderable.

    I would say thats a bit of a flaw and unexpected behavior as the columns.orderable docs state this:

    Note that this option only affects the end user's ability to order a column. Developers are still able to order a column using the order option or the order() method if required.

    Strict adherence to the columns[i][orderable] parameter negates the use of order or order() for those columns. I suggest putting a note in the columns.orderable stating this. Along with a workaround like having a hidden column and using something like columns.orderData.

    Also maybe a note in the order and order() docs stating the usage of columns.orderable may affect server side processing.


  • allanallan Posts: 62,272Questions: 1Answers: 10,212 Site admin

    Hi Kevin,

    Many thanks for flagging this up. I've added an extra example here.

    I actually don't like the orderable parameter being sent to the server-side. I've come to think that it isn't up to the client-side to tell the server-side if a column should be searchable or orderable. It is the other way around!

    Come v2 I'm considering dropping those two parameters in the server-side processing data sent.


  • kthorngrenkthorngren Posts: 20,591Questions: 26Answers: 4,823

    Great, thanks! I can understand these might be useful well at least the searchable parameter.


This discussion has been closed.