Queries and additional scope

[Mark_Slade]

1) My goal:

To learn more about how to use Additional Scope with queries in Retool.

2) Issue:

I have questions about how to use Additional Scope with Retool, and would appreciate any advice.

1. Can I provide additionalScope when using the GUI to test a query, or do I have to use the console?
2. What are the semantics and limitations of using additionalScope this way? For example, unless I'm doing something wrong, I don't believe I can do this (but would like to): {{ categorizedOnly ? 'category_id IS NOT NULL' : ' ' }}.

I've been doing a bit of trial and error to discover capabilities but wonder if there is documentation that goes into greater detail.

3) Steps I've taken to troubleshoot:

1. I searched the forums, but had a difficult time finding anything on it.
2. Read through this similar post from 2022
3. Read through this similar post from 2024

4) Additional info: (Cloud or Self-hosted, Screenshots)

• Self-hosted v3.178.0

[WidleStudioLLP]

Hey @Mark_Slade

You can pass the condition like this {{ categorizedOnly ? 'category_id IS NOT NULL' : ' ' }} in js query where you are using the additional scope

query1.trigger({
  additionalScope: {
    condition: categorizedOnly ? 'category_id IS NOT NULL' : ''
  },
  onSuccess: function(data) {
    console.log("Successfully ran!");
  },
});

[Mark_Slade]

That's really helpful - thank you!

[Mark_Slade]

I see that somebody marked this solved, but I'd like to leave it open. @WidleStudioLLP's answer was much appreciated, but I was looking for something more concrete and complete with regards to how {{ }} substitutions work in queries.

Further, I'm not sure how to apply @WidleStudioLLP's suggestion. I've tried a few things that I thought might work and they don't. The SQL Statement popup show me the correct working query, but what gets executed is different because the error message includes a NULL symbol that the SQL Statement popup doesn't show. I wish I could see the final executed query to help me reverse engineer what happens to my parameters.

I've tried a few combinations but none have worked so far. My working query is:

SELECT t.id, tc.category_id
FROM track t
  LEFT JOIN track_category tc ON t.id = tc.track_id
WHERE ...

1. WHERE 1=1 {{ condition }}
   1. With condition='AND category_id IS NOT NULL', I get a syntax error near ''AND category_id IS NOT NULL''
   2. With condition='' I get a syntax error near ''''
   3. With condition omitted I get a syntax error near NULL
2. WHERE {{ condition }}
   1. Successful query but 0 results in all three cases

I'm wondering if {{ ... }} can only be used the way ? is in traditional parameterized query APIs, such that it can only be used in place of a literal value or list of values, but can't include extra query language like "IS NOT NULL" .

[Tess]

Hi @Mark_Slade,

I agree the popup isn't always as helpful as it should be I recommend keeping SQL syntax, such as AND, IS NOT NULL, etc hardcoded in the query, rather than passing it in as additionalScope. You'll often run into prepared statements issues when wrapping sql syntax in double curly brackets.

Here's some examples:

1. These throw a prepared statements error:
   
   

2. However, if you only pass in a value for categorizedOnly, it works:
   
   

Does that help with your use case?

To your earlier question, I am not sure if I'm following correctly, but we don't have a way to define additionalScope values for testing. You will need to define the additionalScope values in the console or in a JS query/script.

One quirk of GUI mode is that the values will always show as red/undefined, but as long as you define them properly in your Javascript additionalScope, it will work.

Here's a GUI mode example:

[Mark_Slade]

Thank you for the follow-up, @Tess.

On the prepared statement behavior -- that is what I suspected, and appreciate you confirming. It does mean that I can't do what I'd like to (for the same reason I couldn't with traditional prepared statements alone), because the semantics change:

• If I want all: WHERE 1=1 AND true
• If I want only categorized: WHERE 1=1 AND category_id IS NOT NULL

I can't express what I'm trying to do as a single value flip, unfortunately. It seems that whatever you get it is converted into a literal value, so if my {{ }} resolves to a string, it gets wrapped in "the string". I'm guessing my query ends up looking like this:

SELECT ... FROM ... WHERE 1=1 AND "category_id IS NOT NULL"

I looked for docs on additionalScope and curly braces, but didn't find anything covering how it works / expected behavior. If you're taking suggestions -- I would have benefitted from a page or section on "Curly brace substitution in SQL queries" that covers this, or even better would be a tooltip near the editor.

Regarding setting additionalScope in the GUI, got it. I'll keep using query.trigger(). It means dispatching an erroneous query each time I save (because it auto-runs on save), but I can just ignore the errors.

[pyrrho]

Hey @Mark_Slade

One way I've found to execute more complex statements is to make them the output of a JS Query (but not a Transformer) and setup my SQL to do an EXEC with the result.

It's basically an end run around the prepared statement settings so be sure you aren't letting anyone inject things into your components.

[Mark_Slade]

That's crafty - thanks for the suggestion. I think my case is nice-to-have enough that I'm going to just create multiple queries.

[Tess]

Thanks for the feedback, @Mark_Slade! We will keep this in mind for future documentation/tutorials

[Mark_Slade]

Just following up to say that I tested this and it did the trick. It's not how I ever thought I'd implement something but it's good enough for a POC. I have to escape my own values and I'm doing that in a very lazy way. If the stakeholders like it, I'll probably replace it with an actual backend.

I'm using MySQL, so here was the analogous query for posterity:

SET @GETSTATEMENT = {{ queryName.value }};

PREPARE stmt FROM @GETSTATEMENT;
EXECUTE stmt;

DEALLOCATE PREPARE stmt;