Hello developers of retool, I have a pretty insightful question for any takers. I am making an application that uses a snowflake database as the backend data source and I am trying to leverage the retool frontend to filter my table whether then the snowflake table searching as input changes.
I know that common practice in retool with searchbars is a simple query like this:
select * from table_A where column_a like {{'%' + searchBar.value +'%'}}
My issue that I am noticing is that as you type in the searchbar, you are constantly making your snowflake query run as the input changes. I am working with a large dataset in my app (5000+ records) so this common approach severely slows down my app and creates lag. What I noticed using other filtering approaches (select/multiselect components) is that filtering the table directly from the frontend using the filter stack is better than using a where clause based filter and having the snowflake query constantly run.
Goal: My goal is to have a searchbar that will directly filter the table as you type without using the common approach of placing a where clause in the sql query. I want my datasource to pull all the data but the table filters on the front-end using the filter stack.
Steps: I have been playing around with the event handlers of the search bar but I am a bit unfamiliar with running javascript of this nature and manipulating the filter stack, so I need some help there.
To get an idea of the finished product I built this container that holds many different filters (mainly multiselect components) but # search, address, and zip code are the only filters that will use a search bar. Right now I have the search bars in a where clause in my sql query that populates the table I am using but I want to remove that use the search bar within the filter stack.
There are less obvious filter choices like includes and isOneOf which I tried using for my search bar but does not work, so I am trying to better understand the more niche choices and its use cases.
So I know about the search term and the thing about that is that I find it to be good if you use one search bar to do everything. In my case I have 3 and I want each searchbar to be independent of its column, so I need one searchbar for one column.
