I'd like to use the toolbar filter button in the new table component with server-side pagination. Hence I have to integrate the filter config in filterStack into my PostgreSQL query myself, right? This must have been done many times already.
I don't want to re-invent the wheel, but I couldn't find it anywhere. If somebody could point me to a solution that would be great. Shouldn't this be a JS util function?
Edit: Just discovered @Tess' approach to the problem. We'd need something like this but with proper mapping to SQL e.g. for the "includes" operator and with the ability to recursively translate the so-called filter groups.
I came across this problem, and then your question, and then the generally missing solution. I'm sure you've resolved this now, but for anyone new coming to this problem, here is how I've handled it, in case it saves anyone else some time.
This is for mapping Retool's table filters onto PostgreSQL queries to handle server-side pagination.
Ensure there is a version of the Postgres resource that doesn't prepare statements. General best practice is to use these as infrequently as possible. This means JavaScript can be used to compile SQL queries. Use this resource as the table's data source.
Attach two functions to the window via preloaded JS (name things whatever you want obvs):
window.getFilter = (f) => {
switch (f.operator) {
case 'is': return `${f.columnId} = '${f.value}'`;
case 'isNot': return `${f.columnId} != '${f.value}'`;
case 'isBefore': return `${f.columnId} < '${f.value}'`;
case 'isAfter': return `${f.columnId} > '${f.value}'`;
case 'isEmpty': return `${f.columnId} IS NULL`;
case 'isNotEmpty': return `${f.columnId} IS NOT NULL`;
case 'includes': return `LOWER(${f.columnId}) LIKE '% ${f.value.toLowerCase()}%'`;
case 'doesNotInclude': return `LOWER(${f.columnId}) NOT LIKE '% ${f.value.toLowerCase()}%'`;
default: return `${f.columnId} ${f.operator} ${typeof f.value === 'string' ? `'${f.value}'` : f.value}`
}
};
window.arrangeStackFilters = (stack) => {
return `(${stack.filters.map(f => {
if (f.filters) {
return window.arrangeStackFilters(f);
}
return window.getFilter(f);
}).join(` ${stack.operator} `)})`;
}
Note that each nested group of filters is wrapped in parentheses.
In your SQL query, following any other filters you have, include the script:
For my situation, I have a bunch of conditions already applied, so it actually looks like
WHERE ...
{{yourTable.filterStack ? 'AND ' + window.arrangeStackFilters(yourTable.filterStack) : ''}}
columnId comes from the ID assigned in the table, so this should be set there, to be whatever it refers to within this query, e.g. my table reviews_search (rs) has a filterable field text and so the ID of the table column that contains the value is rs.text. Hope that makes sense, and it's weird to be honest to be using a JS string required by a SQL query as a unique ID but here we are.
If anyone has any improvements to this approach, or straight up better suggestions, would love to hear them.
Thanks a lot for posting your solution. I didn't work on this in the meantime, otherwise i would have posted, of course. But now, you ("the internet") solved this problem for me.
Since posting that, I’ve realised an added complication with sorting in our case - I use a temporary table to fetch the data, so the filter happens separately from the sort, which means the columnId needs to be used in two different places (as two different values). I’m going to have to use array indexes I think, so the final select itself looks like
…
ORDER BY 4 asc
…
relating to the 4th column of the table. Probably not relevant to your use case, but lmk if it would help to see it.
But now, you ("the internet") solved this problem for me.