Hello!
I'm trying to implement columns while using server-side sorting. I've found some docs here on server side sort with pagination but they're a bit vague: Display and edit data with the Table component | Retool Docs
I don't see a tables sort or sortObject property. I found some posts in this forum that said I should use the sortArray which I've tried to implement. My example is below:
SELECT * FROM TeamSessions
ORDER BY IFNULL({{dataTable.sortArray[0].columnId}}, 'createdAt') ,IFNULL({{dataTable.sortArray[0].direction}}, 'DESC')
limit {{ dataTable.pagination.pageSize }}
offset {{ dataTable.pagination.offset }};
I added some 'IFNULL' logic since if you only add the variable the query will fail when sort array is NULL. But I'm also finding that since the array (and the IFNULL) returns a string it doesn't sort appropriately. How can I modify my query to enable server side sorting?
Hi @Chad_Laflamme! Just wanted to chime in here that you're right that sort and sortObject don't seem to be exposed on the new Table component anymore -- I've updated the docs to reflect that you use the sortArray property, as you've been using here!
When there is no sort applied to the Table, it looks like sortArray is an empty array ([]) instead of null, so maybe you can modify your query to account for that instead of null? I'm actually a bit surprised that dataTable.sortArray[0].columnId doesn't fail with a "cannot read property columnId of undefined" error in that case
Thanks Mckenna, yes I was surprised it didn't throw some kind of exceptions as well but the null seemed to work.
My bigger problem is that even when the values are not NULL the ORDER/sort doesn't seem to work in the table. If I type out the SORT manually then it works. Is there something else I need to do with the variables in order to get them to work in the query?
Does it work if you check "Disable converting queries to prepared statements" in the resource settings? I think it might be a prepared statement issue since the column ID is dynamic
More documentation on this: Read data from SQL databases | Retool Docs
This post might also be a helpful reference if you don't want to turn off prepared statements for security reasons! Sort columns when server side pagination is enabled - #8 by Kabirdas
1 Like
Thanks Mckenna. This post helped a lot. I ended up using something like this but with many more 'cases':
SELECT * FROM TeamSessions
ORDER BY
case when {{ !dataTable.sortArray[0] }} then createdAt end DESC,
case when {{ dataTable.sortArray[0].columnId == 'createdAt' }} AND {{ dataTable.sortArray[0].direction == 'asc'}} then createdAt end ASC,
case when {{ dataTable.sortArray[0].columnId == 'createdAt' }} AND {{ dataTable.sortArray[0].direction != 'asc'}} then createdAt end DESC
limit {{ dataTable.pagination.pageSize }}
offset {{ dataTable.pagination.offset }};```
3 Likes
thank you so much for sharing your way of doing it, I was struggling trying to find a solution in Postgres! 
1 Like
