Dynamically add filter conditions to SQL

How do I?
,
#1

Hi, I am facing an issue where I want to dynamically add in a filter condition to a SQL statement eg. select * from table where {{filter_conditions}}

The filter conditions is then added via text input: So the text input would be something along the lines of "username = 'test' ". Then the query will execute "select * From table where username = 'test'".

Is there atleast a way to retrieve a list of all the shared queries from the query library inside a table on Retool?

Please any help would be greatly appreciated!

1 Like
#2

@jhtester Can you provide a screenshot/code of what you have already tried?

#3

Hey @jhtester, welcome to the community :hugs:

Try the Query Builder Component, should be a perfect fit for what you're trying to do.

Make sure to disable converting queries to prepared statements in your resource's settings.

#4

This was my first attempt, display_name is a column within my table. Thought it would be possible to perhaps directly inject the text as a variable but its most likely coming through as a string so : Select * from users where "display_name='bob'", which is causing the issue, but im not entirely sure?

Screenshot 2022-01-12 at 08.32.05
Screenshot 2022-01-12 at 08.32.051874×1054 123 KB

#5

Thank you so much for your response this might be what I need. I will need to experiment with it :grinning_face_with_smiling_eyes:

#6

Would it be possible to store the {{queryBuilder.asSql}} value into a table, so I wish to save queries that the user builds and they can then retrieve and edit them? Is there a way to retrieve the query response as text and then convert it to a .asSql type? Inside a transformer maybe?

#7

I think so :stuck_out_tongue:

Can't test this atm but you should be able to just strore the .asSql value wherever and interpolate it in your query as long as you have the prep. statements disabled?!

.asSql -> database -> dbQuery1.data in your query

#8

Hi yes, you have been a massive help thank you so much!!! I was able to do this quite successfully with {{userGroupQuery.asSql.replaceAll('"','')}} just to convert it into a format that the sql DB could understand.

Is there a way to clear the query builder programmatically? I attempted to create an event handler on a button to control the queryBuilder but there are no available methods on the queryBuilder that allows me to clear its value? Is this perhaps a feature request?

1 Like
#9

Nice :))

I don't see any extra functions for the query builder (to interact with via other queries) :cry:

#10

@jhtester you can clear the query builder component programmatically by using a temporary state. you'd set the initial value of the query builder component to this new temporary state and each time you want to update/clear the query builder, you set the temporary states value to whatever you need. Notably, this is how we currently use the query builder to save historically predefined queries. simply replace the underlying QB object using the temporary state.

1 Like