Getting a dynamically built JS query to execute and return the output

App Building
,
1

GOAL
I have an app analyze_results with a table called test_results. The query that I need to populate that table needs to be built dynamically.

  • I have built the javascript that builds the query called getImages. It correctly builds the query and returns the query in the form of a string. I have copy/pasted that string into the query library editor and run it, and it works perfectly
  • What I can't figure out is how to get that query to execute in the app and then display in the test_results table

STEPS SO FAR

  • Configured Retool to allow the running of JS inside a SQL query
  • Created a resource query against the retool database and then simply pasted {{getImages.value}} into it, hoping that it would simply import the query text and run it.
  • tried writing the non-dynamic parts of the query in SQL, and then calling the JS for just the dynamic portions
  • Neither of those things worked

Details
Here is the Javascript

function buildDynamicQuery() {
  // Get the ordered models from the reorderable list
  const modelsInOrder = orderedModels.value; // Accessing the array of ordered models

  // Start building the query string
  let query = `
    SELECT
      d.data->>'prompt' as model,
  `;
  
  // Dynamically generate the MAX statements for each model
  modelsInOrder.forEach((model, index) => {
    query += `MAX(CASE WHEN d.data->>'model' = '${model}' THEN d.data->>'image' END) AS col_${index + 1}`;
    if (index < modelsInOrder.length - 1) {
      query += ', ';
    }
    query += '\n';
  });

  // Add the FROM, JOIN, WHERE, GROUP BY, and ORDER BY clauses
  query += `
    FROM data d
      JOIN datasets ds on d.dataset_id=ds.id
    WHERE 
      d.data->>'prompt_dataset' = 'jewelry V10.0.0'
      AND ds.dataset_type_id = 17
    GROUP BY 1
    ORDER BY 1
  `;

  return query;
}

return buildDynamicQuery();

And here is what the output of that Javascript looks like. This is correct SQL and returns the correct output when I run it manually in the query library

:" SELECT d.data->>'prompt' as model, MAX(CASE WHEN d.data->>'model' = 'control: Base SD2_1' THEN d.data->>'image' END) AS col_1, MAX(CASE WHEN d.data->>'model' = 'control: Base SDXL' THEN d.data->>'image' END) AS col_2, MAX(CASE WHEN d.data->>'model' = 'control: jewelry_arcade_v7_053024_2790_v21_10000' THEN d.data->>'image' END) AS col_3, MAX(CASE WHEN d.data->>'model' = 'control: jewelry_arcade_v7_053024_2790_vxl_15000' THEN d.data->>'image' END) AS col_4, MAX(CASE WHEN d.data->>'model' = 'jewelry_arcade_v10_061424_3872_v21_10000' THEN d.data->>'image' END) AS col_5, MAX(CASE WHEN d.data->>'model' = 'jewelry_arcade_v10_061424_3872_v21_15000' THEN d.data->>'image' END) AS col_6, MAX(CASE WHEN d.data->>'model' = 'jewelry_arcade_v10_061424_3872_v21_20000' THEN d.data->>'image' END) AS col_7, MAX(CASE WHEN d.data->>'model' = 'jewelry_arcade_v10_061424_3872_v21_5000' THEN d.data->>'image' END) AS col_8, MAX(CASE WHEN d.data->>'model' = 'jewelry_arcade_v10_061424_3872_vxl_10000' THEN d.data->>'image' END) AS col_9, MAX(CASE WHEN d.data->>'model' = 'jewelry_arcade_v10_061424_3872_vxl_15000' THEN d.data->>'image' END) AS col_10, MAX(CASE WHEN d.data->>'model' = 'jewelry_arcade_v10_061424_3872_vxl_20000' THEN d.data->>'image' END) AS col_11, MAX(CASE WHEN d.data->>'model' = 'jewelry_arcade_v10_061424_3872_vxl_5000' THEN d.data->>'image' END) AS col_12 FROM data d JOIN datasets ds on d.dataset_id=ds.id WHERE d.data->>'prompt_dataset' = 'jewelry V10.0.0' AND ds.dataset_type_id = 17 GROUP BY 1 ORDER BY 1 "

Any and all help is appreciated!

2

Since you are already building the query in a JS Statement, you could try to declare a SQL parameter that is set to the value of that statement, and then EXEC(@theStatement).

1 Like