Dynamically select which column to search in Retool DB

App Building
,
1

Greetings, fellow coders, I am in need of assistance.

I have this Select object called classSelect, with a number of values to select from.
I need to create a query on the retool-hosted database, where I filter on the value of some boolean columns, but which one of said columns depends on the selected value of classSelect.

Behold, the different columns of boolean values. I want to run my filter on one of the three columns.

image

My problematic code:

SELECT
  *
FROM
  mydatabase
WHERE
  {{classSelect.value}} is TRUE
  AND (
    some other filters that run fine;

The above is not working and I am getting the following message with no further info, where "bachata" is classSelect.value.
image

As a workaround, I have created mutliple queries where I use the specific names of each column and a switch(classSelect.value) / case JS to select which one to run, but this isn't very elegant.

I'm still new to Retool but I've got enough coding experience in unrelated languages to dislike this clump of duct tape where I'd rather have a nice clean welding.

Any suggestions on how to make a cleaner job of this?

Thank you!
Doc

2


Hey @DocShades!

One way to do this without disabling prepared statements is to do something like:

SELECT
  *
FROM
  mydatabase
WHERE
({{classSelect.value !== "bachata"}} OR bachata is TRUE)
AND ({{classSelect.value !== "salsa"}} OR salsa is TRUE)
AND ({{classSelect.value !== "kizomba"}} OR kizomba is TRUE)
AND (
  some other filters that run fine
);

In each case, if the option isn't selected the whole clause defaults to true and is effectively ignored by the AND operator. Does that work?

3

Worked like a charm, thank you!