Although server-side pagination is supported - and can be easily be referenced in the query - I was not able to do so for sorting. Whenever I try to use the following in my query, this is not supported:
ORDER BY {{ table1.sortArray[0].columnId }} {{ table1.sortArray[0].direction }}
Also, in my opinion, given the many instances where one would simply use this, I would argue for being able to simply provide the following short-hand:
table1.sortQuery
In the above (fictional) example, this would result in the same query as the one I mentioned above. Although I can imagine the ability to sort/order against multiple columns, and correct me if I'm wrong, but I don't think this is currently supported in the table component.
I understand there is an option to disable query safety altogether, but I think that is killing a fly with a sledgehammer, in my opinion. I think Retool has all of the relevant variables to safely determine if the provided column name is in fact available in the set of columns that is available inside the table. So regardless of any binding issues or security concerns, I think this should simply be a matter of additional validation behind the scenes.
To be clear, I find the way of supplementing queries with the LIMIT and OFFSET clauses based on the component's pagination object to be highly convenient, and I applaud the 'separation of concerns' this provides to developers.
Thank you for the well written out explanation and examples.
I completely agree, there definitely can be a middle ground where users don't need to disable query safety. I do not think it would be too tricky to check for column name in the data payload the query is receiving.
So for the order of operations, you are fetching data, then applying filters in the app UI (such as clicking on a column name in the table component to order by DESC/ASC) and then you want to run a second query that is sharing the applied filters, correct?
I can file a feature request for this and will keep you updated on any news I hear from the engineering team on this!
Thanks for taking the time to respond to my question/request. After your remarks, I do see the contradiction between the initial rendering and the secondary UI controls. More concretely, given that there is no default order column to begin with, it can't be included in the query by default. Unless there is an option in Retool to use a default column (e.g. "ID"), there is no way of knowing which column to order by.
Perhaps one strategy could be similar to how server-side pagination works with the total amount of rows. In the same way, an Addon for "Sorting"/"Ordering" could be made available, providing the option to set default values for sortArray[0] (i.e. columnId and direction).
That may help establish default values for the order clause to be used initially and changed dynamically whenever the user chooses to sort manually.
Another strategy would be to conditionally use a different data source for the table, depending on whether or not sorting is applied. In the latter case, a different query is used altogether. But this approach isn't quite that DRY, especially if the query becomes longer and more tedious to manage in multiple places.
Hope you can figure it out, and I'd be happy to reflect on any suggestions you may have in the meantime.