How to add a number filter outside of the table?

Queries and Resources
1

I have the following query and the first two filters for shipper and lane work, however the "priority" one doesnt. The query wont break but the filter is not working. I am using the multiselect retool component. Help please!

select * from lanes where shipper ilike {{"%" +ShipperSearch.value+ "%"}} and lane ilike {{"%"+moveFilter.value+"%"}} or priority = ANY({{PrioFilter.value}})

2

Hey @carolina! What do you mean by "not working" - what are your expected results, and what is the query actually returning?

3

The filter won't return the values I input. I updated the code to:

select * from quotes.lane_priority where shipper ilike {{"%" +ShipperSearch.value+ "%"}} and lane_desc ilike {{"%"+moveFilter.value+"%"}} and CAST((CASE WHEN priority IS NOT NULL THEN priority ELSE -1 END) AS VARCHAR) ilike {{"%" +PrioFilter.value+ "%"}}
order by priority, weekly_volume DESC

I had to use VARCHAR to tell the query to look at the values of "priority" as a string instead of integers. Now the filter returns the whole data set and does filter based on the input (either, priority=1, 2, or 3) but returns the records with values "1" as well as "10"

4

Hi @carolina,
Not sure what values you are referring to with "1" and "10", I am just supposing that since they are treated as string; any values containing "1" will be true.

The earlier query you have seems fine, I think you just need to group the statements. That is

select * from lanes where shipper ilike {{"%" +ShipperSearch.value+ "%"}} and ( lane ilike {{"%"+moveFilter.value+"%"}} or priority = ANY({{PrioFilter.value}}) )

Again, I just assumed moveFilter and PrioFilter values are what can either be true. Since PrioFilter is in multiselect, this should be fine. Just make sure you are getting the value you want (check if you want the value or the displayedValue)

Let us know if that works

1 Like