How to write this condition correctly to where clause?

Queries and Resources
1

I need to make the following condition compatible with retool query:

WHERE
 ({{ !notes.value }} OR {{ notes.value == 'with_notes' ? orders.shopify_note != '': orders.shopify_note == ''}})

If the dropdown has a value selected and its with_notes result query should look like this:

     WHERE
        orders.shopify_note == ''

If the dropdown selected value is without_notes the query should look like this:

 WHERE
    orders.shopify_note != ''
2

@ckissi I’m not sure what you mean / how we can help. What are you looking for exactly?

3

The condition above doesn't work in the SQL. How to write it to make it compatible with Retool parser? It's a regular JS syntax but in retool it doesn't work.

4

notes is a dropdown element.

image

5

What error are you getting? Can you send over a screenshot?

6

I've tried different approach but doesn't work too

image
image1199×569 97.1 KB

7

Could you try removing the full quotes? So orders.shopify_note <> ''

8

image
image1333×288 59.4 KB

This actually the initial state from the beginning of this thread.

9

Just replace <> with !=

1 Like
10

@nacho thanks but if you check my first code in this thread you’ll notice it uses what you recommend. Unfortunately, does not work.

11

The error you’re getting here is a SQL error. It’s saying that shopify_note is a boolean, therefore you can’t compare it against a string.

I think you mean this (or the reverse):

notes.value == 'with_notes' ? "orders.shopify_note": "NOT orders.shopify_note"

In any case, I would try it in plain SQL first and once that works, try to interpolate parts of it in JS.

12

Unfortunately no, the orders.shopify_note is text and the error is related to SQL binding thats used in Retool.

image

In plain SQL it works OK.

13

Hi @ckissi,

I think the issue here is we’re getting SQL and JS syntax mixed together. <> is SQL syntax for not equal to, but not valid inside of the {{ }} tags which are all javascript.

What you’re looking for might be:

AND ( {{ !notes.value }} OR )

Passing the results of a {{ }} tag as a string to be read as SQL syntax is only possible if you disable prepared statements in the resource setup, since we want to prevent someone writing DROP table inside of a text input. If you need to pass in a string to be read as SQL, you can disable prepared statements in the resource setup!

14

@alex-w
So if I understand right in SQL I can use only JS syntax that returns boolean value right?

Hmm, I’m confused now because this part works ok and it uses a string as a result for ANY statement:

(true = {{ fulfillment_status_multi.value.length == 0 }} OR orders.fulfillment_status = ANY ({{ fulfillment_status_multi.value }}))