Thanks for Retool, it's really cool and you can definitely be proud of it.
I'm having trouble with something simple though. I have a table and need to add custom filters as separate UI elements - very similar to what's done in the intro video. However, the DB query is expensive and slow so I cannot rerun it every time these UI elements are changed (e.g. value selected in multi-select). Instead, I'd like to rerun it in JS, which is much much faster, providing a snappy user experience.
I've tried a few approaches, but keep running into walls.. Here's what I've tried:
1. Transformer on query + caching the query
Enable caching on the query and attach a transformer to the query output directly to filter it there. Ideally, when the UI elements trigger the query, it'll reuse the SQL results, but rerun the JS transformer. However, that doesn't happen and it reruns the SQL.
2. Separate transformer
I wrote a simple JS transformer for filtering (see below) and use the output of this transformer as the input for the table. I actually like this approach of keeping the JS filtering separate and triggering that independently. However, there doesn't seem to be any way to trigger this transformer from the UI elements so while the filtering works one time, it cannot be updated.
3. Query with run JS code resource
I tried making a query with resource "Run JS Code" and copy the code from (2) there. However, for some reason this JS code cannot reference values from other elements (e.g. {{filterName.value}}) and I get a syntax error Missing semicolon.
How do I do the table filtering in JS? Also, how do I trigger a transformer or use values from UI elements in a JS code query?
// Extract dataset and filter values
const nameSearch = {{filterName.value}}.toLowerCase()
const selectedLanguages = new Set({{filterLanguage.value}})
const selectedApps = new Set({{filterApps.value}})
const influencers = {{fetchInfluencers.data}}
const columnNames = Object.keys(influencers)
// Prepare output as separate arrays (expected by retool)
let output = {}
for (const k of columnNames) {
output[k] = []
}
// Filter data
for (const row of formatDataAsArray(influencers)) {
const { name, language, games } = row
// Rows are only allowed if they fulfill all criteria
if (nameSearch && !name.toLowerCase().includes(nameSearch)) continue
if (selectedLanguages.size && !selectedLanguages.has(language)) continue
if (selectedApps.size) {
// Compute intersection to check if influencer has at least 1 app
const overlapSize = games.filter(a => selectedApps.has(a)).length
if (overlapSize === 0) continue
}
// Add to each column (stored independently in retool)
for (const k of columnNames) {
output[k].push(row[k])
}
}
return output
