Hi!
I'm wondering how people typically chain SQL queries in Retool. I have a bunch of 'Query JSON with SQL' Resource queries that depend on each other. For simplicity, say I have:
js_query:
return [
{ location: "East",
amplitude: 4000,
category: "first"
},
{ location: "West",
amplitude: 300,
category: "first"
},
{ location: "East",
amplitude: 400,
category: "second"
},
{ location: "West",
amplitude: 3000,
category: "second"
}
]
sql_query:
SELECT * FROM {{ js_query.data }}
WHERE category = 'first'
The behaviour I would like to achieve is that whenever I run sql_query, js_query is run first to get the newest data. I'd like this behaviour whether the first is a js query or another sql query or whatever. Things I tried were:
- I looked for a field where I could specify js code to run on trigger before the query is executed, but couldn't find any.
- I tried to add the code into the query, but this failed. I tried:
{{ js_query.trigger() }}
SELECT * FROM {{ js_query.data }}
WHERE category = 'first'
(this is not allowed)
and also:
SELECT * FROM {{ (() => { js_query.trigger(); return js_query.data; })() }}
WHERE category = 'first'
Which is also not allowed.
(both of these seem unnecessarily complex anyway & trigger is probably async but they illustrate what I want to do)
What I would like is to be able to specify a list of queries as dependencies which get evaluated automatically before my SQL query gets evaluated.
I suppose I can resort to js queries which trigger other queries manually and then transform my data in js (possibly using AlaSQL). But in general, is there a way of chaining queries using a pull-based evaluation strategy?
