Help needed: fuzzy match behavior is different between Select and Table?

[caleb]

Goal
I am trying to implement a table search that is very flexible.

I would like to replicate the behavior of searching for an option in a Select component (with "Fuzzy match" set) in a table search.

Setup
I tried setting a table search to "Fuzzy match" and linking it to a Text Input as the Search term.

I have set up a table with the Demo data and linked to the text input in the top left.

Problem
When I search for "Ken Worling" no results are found in the table. A Select component with the same data will find the person being searched for.

Question
Is it possible to use the search algorithm that the Select component uses in my table?

[ScottR]

@caleb This should work. Remove the select field.... and try simply refreshing the app....

[MiguelOrtiz]

Hey @caleb,

I was able to reproduce this, so i think the retool team will need to chip into this thread with info on how the fuzzy search is working in the table and if they're able to push a fix so that it works the same as the select component.

@ScottR, in your example you only wrote worling, which indeed returns a match, but what Caleb wants to achieve is that Ken Worling returns a match (in the same manner as the select type search does)

[ScottR]

There is a way that you can adjust the query populating the table by using something similar to:

yourdatabasesourcename.first_name ILIKE {{ '%' + searchBar1.value + '%' }} OR
    yourdatabasesourcename.last_name ILIKE {{ '%' + searchBar1.value + '%' }} OR
    yourdatabasesourcename.email ILIKE {{ '%' + searchBar1.value + '%' }} OR .....

[caleb]

Hi Scott – yes this is one route I've considered, however I'd like to pre-load the data rather than re-querying with every search bar update for performance. Also it is a bit complicated to re-implement the flexible way the fuzzy match works in an SQL query.

[ScottR]

You could store the data in a variable and search on that using Query JSON with SQL like so...

WHEREE firstname = ANY({{textInput.value}}) OR lastname = ANY({{textInput.values}}))  ....

[Jack_T]

Hi all,

To @MiguelOrtiz's point I can definitely dig into why there is a disparity between the two search methods. I can make a feature request for this

@ScottR's custom query using 'Query JSON with SQL' should be a solid temporary work around.

Another great option would be using the Filter component, which has a nice option to link it with a table and allow for column and other specificity as well!

[Jack_T]

Just filed a ticket to allow users to toggle between "full/exact" matching. which the fuzzy search currently has with the "partial" matching. that the select component uses.

Will update this thread with any news on the ticket. For now, the best work around would be the filter connected to table for searches