In the documentation, there is some information about built-in sorting feature.
But as I have a server side pagination query to populate a table, I guess this is not working in my case.
So, I have tried to apply the sorting to my SQL query.
Not sure what I'm doing wrong, or if it's a bug.
I have copied the result of the "sql statement request" green hint (with the values correctly replaced as displayed when hovering the question marks) and it works through a regular mysql query directly on the server.
But as soon as I run the query on ReTool it's not sorting at all.
Here is the query :
SELECT
d.id as domain_id,
d.url,
d.deleted,
GROUP_CONCAT(DISTINCT u.email, " (", u.id, ")" SEPARATOR ", ") AS emails,
d.created_at as domain_created_at,
p.name as plan_name,
s.stripe_status
FROM xxx_domains AS d
LEFT JOIN xxx_accounts AS a
on d.account_id = a.id
LEFT JOIN xxx_account_user AS au
ON au.account_id = d.account_id
LEFT JOIN xxx_users AS u
ON u.id = au.user_id
LEFT JOIN xxx_subscriptions AS s
ON d.id = s.domain_id
LEFT JOIN xxx_subscription_items AS si
ON si.subscription_id = s.id
LEFT JOIN xxx_plans p
ON si.stripe_plan = p.stripe_id
WHERE d.deleted_at IS NULL AND
( {{ !input_domain_filter.value }} OR d.url LIKE {{'%' + input_domain_filter.value + '%'}} ) AND
( {{ !input_email_filter.value }} OR u.email LIKE {{'%' + input_email_filter.value + '%'}} ) AND
( {{ !select_status.value }} OR s.stripe_status = ({{ select_status.value }}))
GROUP BY d.id, s.stripe_status, p.name
ORDER BY {{table_domains.sortedColumn ? table_domains.sortedColumn : 'created_at'}} {{table_domains.sortedDesc ? 'desc' : 'asc'}}
LIMIT {{ table_domains.pageSize }} offset {{ table_domains.paginationOffset }};
The order by is correctly populated with "domain_created_at desc" (or any other sortable column) but the result is always the same (seems to be the default mysql order).
Not sure if it's related, but in the preview mode (or editor) the table is loaded with 15 items while it should load 50. I have to press the reload button to get the 50 items (unsorted).
This certainly looks odd. Would you mind posting the value of the .query property for that query when it returns a faulty value? I'm also curious what kind of SQL resource this is.
I don't know where is the .query property, could you let me know how to find it?
It's a MySQL 8 resource.
But, I have enabled debug trace on the Mysql server and I think I found the issue.
Here is the part which is not correct : ORDER BY 'domain_created_at' 'asc'
It should be ORDER BY domain_created_at asc
It's because I use prepared statements in general configuration.
And I as replaced manually the ? in the prepared query to check it directly on my server, I didn't add the single quotes around the order by.
Is there a way to insert a variable specifying it has not to be prepared?
I saw another topic where someone suggested duplicating the resource with prepared statement option disabled and use this resource for this query.
About the table loading 15 items, in fact it was 10.
And it seems to be because Retool uses the "max page size" default value, even if the layout height is set to fixed.
I had to do that because we wanted 50 items by default in the table.
But I think there is a bug here, as when you set a higher table to load more result at once, the pagination UI still count 10 items per page (or the value previously set in the max page size input).
Seems like there is somewhere in the code a mix between the two ways of calculating the pagination.
Ah good catch! Unfortunately, there isn't a way to have specific variables not be prepared, the setting only exists on the resource level.
As for the page size issue, that is indeed a bug, thanks for surfacing it, and catching the cause - that made reproducing the behavior straight forward and I'll pass it along to the dev team to take a look. We'll let you know here when there's a fix!
In the meantime, can you try adding your table's page size as a watched input for your query so that it automatically reruns when the page size recalculates?
Or am I misunderstanding and does the reloading not fix the issue here?
That should work, Postgres doesn't support passing prepared statements to ORDER BY clauses (this post has some more info), but turning it off will let you do so. Just be aware that you lose some SQL injection protection when you do! We usually advise people to make two separate resources, one with prepared statements on, and one with it off, so that you only need to use the latter where necessary.
