My Query is working in MySQL, but not in Retool

App Building
1

  • Goal: When I execute the following query in MySQL, it works, but in retool, the query returns nothing. I'm trying to find out why.

  • Steps: I have a query designed to fetch a user based on either their full name, email, or username. When I enter a value into the appropriate inputs and press run within the Retool editor, I get no results back, which is unexpected behavior. When I copy over the query from Retool into MySQL and run the query, it returns results as expected. I suspect this has something to do with how Retool processes queries, but I'm not sure how to debug this.

  • Details: This is the query as typed in Retool:

SELECT id, first_name, last_name, email, username, user_class_role AS role FROM user
WHERE platform = "enterprise-web"
AND (
email LIKE {{emailInput.value ? '%${emailInput.value}%' : '-1'}}
OR username LIKE {{usernameInput.value ? '%${usernameInput.value}%' : '-1'}}
OR first_name LIKE {{firstNameInput.value ? '%${firstNameInput.value}%' : '-1'}}
OR last_name LIKE {{lastNameInput.value ?'%${lastNameInput.value}%' : '-1'}}
)

This is the query as copied over into MySQL:
SELECT id, first_name, last_name, email, username, user_class_role AS role FROM user
WHERE platform = "enterprise-web"
AND (
email LIKE -1
OR username LIKE -1
OR first_name LIKE '%Chris%'
OR last_name LIKE -1
);

In Retool, the query seems to filter all entries out, whilst in MySQL it returns back the handful of results that I would expect. Why might this be?

2

Hi @Corey_Rozenblat,

Welcome to Retool!
You're close, I think it's just your string interpolation that needs a bit of tidying up. I'll put it in a code block so the quotes and tilde formatting are clearer.

SELECT id, first_name, last_name, email, username, user_class_role AS role FROM user
WHERE platform = "enterprise-web"
AND (
email LIKE {{emailInput.value.length > 0 ? `%${emailInput.value}%` : '-1'}}
OR username LIKE {{usernameInput.value.length > 0  ? `%${usernameInput.value}%` : '-1'}}
OR first_name LIKE {{firstNameInput.value.length > 0  ? `%${firstNameInput.value}%` : '-1'}}
OR last_name LIKE {{lastNameInput.value.length > 0  ? `%${lastNameInput.value}%` : '-1'}}
)

Main changes here are using the tildes ` to wrap the string you're creating, as well as modifying how you check for the value. Value will exist on the inputs no matter what, so I've modified the code to check for the length of the input, and then act accordingly. Give this a shot and see if it gives you the result you're expecting.

1 Like
3

That seems to have fixed it. Thanks Mike! But why would the query work in MySQL, but not in Retool. Can you give any insight into that aspect of things?

4

Hi @Corey_Rozenblat,

There wasn't really anything wrong with your query, just with the way the values were getting inserted via javascript. Anything you put between {{ and }} in Retool is essentially Javascript.

I think what was actually happening was your query was turning this:

email LIKE {{emailInput.value ? `'%${emailInput.value}%'` : '-1'}}

into

email LIKE "'%%'"

Notice the double quotes AND single quotes around the value, and it was doing that for all of your LIKE clauses.
Breaking this down...

`'${remailInput.value}'`

Putting tildes (`) around the text allows you to do string interpolation in JS, so

`${emailInput.value}`

is correct, but since this is a String, it gets wrapped in " " when it gets injected into the SQL statement, your extra quotes were being treated as extra quotes in the string.

Lastly, because all of the inputs technically had a value - even if it was blank, it was never reaching the "else / : " clause in your ternary operator. emailInput.value always equals true, so it never falls back to -1.

5

I see, that makes sense. Thanks again for your help Mike!

6

sorry this is totally random and off topic but since the typeo's there.... ReTool should ReName the email ReSource to ReMail :upside_down_face: :pleading_face:. yup i went there, but I'm done now, again sorry :rofl:

3 Likes
7

Hey @MikeCB, I have been facing a similar problem with a SQL query of mine it would be great if you could help me with my sql query too. It seems that the query is executed but there is no data in retool but when i am running the same query on my SQL DB it is fetching that data.

My SQL QUERY:-

select * from reporting_order where 
brand_id ={{ select1.value ? `'${select1.value}'` : '1=1' }}
AND order_date <= {{ date1.value ? `'${date1.value}'` : '1=1' }}
AND order_date > {{ date2.value ? `'${date2.value}'` : '1=1' }};

Looking forward for your reply.
Regards.

8

Hi @Shivam_Sharma,

Can you show me the working query in SQL so I can have a better understanding of what you're trying to accomplish. An example with and without dates and a value selected. From what I can see, if nothing is selected in select1, then you'll end up with WHERE brand_id = 1=1

9

Hey @MikeCB, thank you for replying but I have accomplished this by directly using values of these components in the sql query using a simple JS script, but for anybody else if this could be useful, I used the following queries

JS script:-

const bclause =select1.value
const dclause =date1.value
const dclause2 =date2.value
return [bclause, dclause, dclause2];

SQL Query:-

select * from reporting_order where 
brand_id = '{{query2.data[0]}}'
AND order_date >= '{{query2.data[1]}}'
AND order_date < '{{query2.data[2]}}';

and i made my this sql query the data source of my table, also the the run behavior of this sql query was manual and was triggered by a button.