I know this topic has been covered in the "Select Component" documentation and some other post. Yet I still have the issue after following all the steps.
I want to filter my query by the value of an input text component and the value of a dropdown component. I have catered the {{!dropdownComponent.value}} scenario but the query does not return any rows when the dropdown is empty.
Hey @oliver.ng and welcome to the forum! It looks like your issue might relate to the bcn_input part of your query? You’ve added in the null handler for the select_customer component but not the bcn_input one.
@justin Thanks. Not really. I simplified the view to have only a table (hooked up with a query called "test") and a dropdown select. Rows are returned in the table when dropdown is not null. The null handler somehow doesn't work and no rows are returned when dropdown is null.
This is interesting, and I’ve been able to repro locally with BigQuery. They basically don’t want to handle null values at all. The reason this isn’t working as it does in the docs is that you’re using = instead of ILIKE, the latter of which can handle null values. So there are a couple of ways around this: you could set a default value for the select component, or find a way to handle the null in SQL.
Something like this should work:
WHERE {{ select1.value ? customer_name = select1.value : true }}
@oliver.ng this is a BigQuery error - I don’t think it maps to anything in Retool. It could be a weird setting on your end, but it’s not something I’m seeing when I repro this locally.
I'm having a very similar problem. Not sure how to optionally filter bq tables using a potentially null input value.
I tried something like WHERE {{ select1.value ? customer_name = select1.value : true }}
but (using your example) customer_name isn't defined in the curlies. I also have a join in the query, so I would have something like a.customer_name which really made the javascript unhappy.
@atorres makes sense that this isn't working, since we're converting the output of those curly braces to a string, so BQ probably thinks you're saying something like WHERE "some_random_string". We really should put together a few best practices in our docs here (that's on me).
To make sure I understand what you're trying to do, you've got a select component, and you want logic along the lines of:
If there's a value in here (i.e. non-null), then filter the table for that value
If there's no value in here, then don't filter the table for it at all
I think the workaround might be (unfortunately) to use regex:
WHERE REGEXP_CONTAINS(customer_name, {{ select1.value | .* }})