How to use the transformer inside the SQL query?

Is it possible to use transformer in the Where statement of the SQL thats associated with the table data?

    WHERE
  ac.email ILIKE {{  '%' + emailSearch.value + '%'  }}
  AND cu.first_name || ' ' || cu.last_name ILIKE {{  '%' + nameSearch.value + '%'  }}
  AND (ac.phone_number ILIKE {{  '%' + phoneSearch.value + '%'  }}  {{ phone_sql.value }}) 

{{ phone_sql.value }} is a transformer

transformer looks like this:

const empty = "";
const query = " OR ac.phone_number is null";
if({{ phoneSearch.value }} !== '') return empty;
return query;

I’m getting this error: syntax error at or near “$4”

To me it seems like retool is using standard binding for params in SQL. This is why it ignores complex SQL like string.

Is there a workaround for this?

Hey ckissi – you have to explicitly set this setting on your database resource. Go to the resource settings page and check “Disable converting queries to prepared statements”, then it should work how you expect!

When I click the checkbox “Disable converting queries to prepared statements” and I click “save” it shows message that it was saved but after refresh its unchecked again.

Hmm, I just tried it on my end and it worked. Does this persist after a refresh?

Hmm, I’ve tried now again and I cant save it. When I refresh the page box is unchecked.

@ckissi Could you try setting up another copy of the resource with the option disabled? It could be good to keep that separate anyways to limit the surface area that you need to worry about SQL injection on. I’m thinking this could have something to do with the active SSH tunnel, but if you have more issues message us in on the blue chat Icon inside Retool so we can get some account specific info from you about the setup.