I noticed that some columns seem to sort unreliably in one or two tables I had created using the table generator. I examined one particular table; the correct ORDER BY clause was included in the queries for each column and yet one column would not sort correctly - or at least the displayed order would not update. It looked strange, because the other columns were reordered, and yet the column used for sorting remained in the same order - so the data in the same row belonged to different records (rather like what can happen in Excel).
I discovered the only difference with the problem column was that the data was in a data attribute:
<td dmx-text="publication_sortdate" class="text-center"></td> -- sort order would not update
<td class="text-center">{{publication_sortdate}}</td> -- sort order correct
Another detail is that the sort order initially seems to work but it won’t switch/toggle - eg it will remain in descending order.
Is this a limitation of using attributes or a bug… ?
The issue I had was related to having empty values in some records - this causes sorting problems. A solution is given in the first of @psweb’s productivity tips. However, I don’t think is related to your problem - but it’s an important thing to be aware of.
I haven’t come across any problems sorting in the situation you describe, and hadn’t noticed that the same column might appear twice in the order by list - however, looking at the generated query, I can see that it does.
I’m using MySQL, which doesn’t seem to mind that the query might contain this: ORDER BY stock_id ASC, stock_id DESC
Are you using a different type of database - which, quite reasonably, flags this as an error? It seems like this might be a bug, and that default sort parameters should be removed in this situation.
I don’t have any experience with MS SQL Express, but I think you should be able to resolve the sorting issue by adding a condition to the default sort column in query builder:
Hello @TomD, I’ve tried what you have suggested but after saving the changes, the page won’t load anymore. I’ve also tried to remove the bang (!) and it shows the same error message. I’ve also tried to set a default value (in formatter) as DESC, it still shows the same error message.
I tested it with PHP/MySQL setup and it worked. I tested it with and without the ! and it worked as expected in each case. I use conditions like this a lot, and I’ve never known them to cause a page not to load (but this is only with PHP/MySQL).
Do you have a column defined for sorting or do you only use dynamic sorting. The error states that you have the same column twice in your order.
If you want to use a default column then use $_GET.sort.default('stock_id') for the sort expression. Leave the sorting in the query builder empty. @TomD suggestion with the Condition should also work. What is the error you get when you set the condition?
a) Set Column defined for sorting in query options = has error when trying to click header in page’s table to sort the same column
b) No column defined for sorting in query options = no errors when trying to click header in page’s table to sort the same column
The error with the condition seems to be something with our .net code, could you post the orgException with the stack-trace. Will try to fix that for the upcoming update.
If you remove the sort from the query builder, is the table sort then working?
Hello @patrick, I am sorry I was not able to get back to you immediately. I have moved forward into creating the web app since I am on a deadline. The workaround I did is to manually define the sort and dir parameters in the URL instead of using the Column sorting in the Database query in Server Actions Step. Since the original purpose is to have the table to be sorted by a column by default, setting the URL manually in a link works. I don’t have any column sorted in the database query in the Action Steps. So all of my pages that needs to have table sorted by default are loaded in the browser using URLs with the sort and dir manually defined. I will try to test it out again and will send you the information you have requested.
