Referencing another query in a query

App Building
1

  • Goal: I want to run SQL on another query result directly, because I have some base tables doing some complicated joins and I want to do a bunch of things to that same logic without copying the SQL ... and I want to do it with SQL not javascript which seems to be what the transformers are explicitly for.

  • Steps: I've got each part working fine, and can put in a query essentially

SELECT * FROM (
SELECT * FROM baseTable
) AS filteredTable
WHERE etc etc

I'd like to just replace the whole inner part with a query that already has that in it, so it'd be

SELECT * FROM (someQuery)
AS filteredTable
WHERE etc etc

Is that possible? It seems like it'd make code maintenance much easier.

2

I think there're a couple ways you could do this:

  • add/replace function (or stored procedure) to retool db

    • functions return a value
    • stored procedures do not return values
      image

  • chain Retool DB Query and Query JSON With SQL resource queries

    • first, query your db and return the results
    • use SELECT * {{ formatDataAsArray(query_name.data) }} in a Query JSON With SQL query
      image
      image
    • formatDataAsArray() is required and can either be called once in a transformer(which you're wanting to avoid) or it can be called directly anytime a queries results need be further queried, this is because the Retool DB Query by default returns an object containing 1 array for each column:
      
          | column1_name   | column2_name |
----------|----------------|---------------|
row1      | c1_row1_value  | c2_row1_value |
row2      | c1_row2_value  | c2_row2_value |
      SELECT * FROM table1 would return:
      {
  column1_name: [c1_row1_value, c1_row2_value],
  column2_name: [c2_row1_value, c2_row2_value]
}
      formatDataAsArray() turns the above into the following:
      [
    {
       column1_name: row1_value,
       column2_name: row1_value
    },
    {
       column1_name: row2_value,
       column2_name: row2_value
    }
]
3 Likes
3

Thanks, I ended up ditching the retool database and using SQL Server, which lets me use views and stored procedures to pretty much accomplish what I needed here.

4

if that's easier for you then ya that'd be the best solution =)

Retool DB is a postgresql database in the backend, you can connect to it using the same tools like DBSchema, DataGrip, and i think MySQL Workbench will even connect, then add your views and procedures from there. the only difference is who hosts and sets up security (other than built-in differences between postgresql and sql)

2 Likes