Fuzzy Search / Words not in order

Neil Harrington · May 2022

Hi.
Anyone know how to search when the input words don't match up to positions in the string to search?

Problem:
Example database value: "The quick brown fox jumps over the lazy dog"
Input string of "quick brown" -- should return this record
Input string of "brown quick" -- should return this record
Input string of "fox quick" -- should return this record
Input string of "grey quick" -- should return not return this record
(caveat: The input string could be any number of words)

I'm hoping to be able to do this in a Query All statement, as having to loop will likely take much longer.

I've tried splitting the input string into an array and using contains and/or in. That 1/2 worked, but all 3 of the above examples returned a record.
I've tried looking into regex expressions, and haven't yet had success.
I thought maybe to do looping with a 'do all the records of this input array exist in the field array' -- but array manipulation doesn't seem to do that.

Thoughts?

Thanks.

Neil.

Ray Deck · May 2022

I would break up the search into words and then loop over the words to generate multiple query all statements and feed the results onto a larger array. The you can de-duplicate the array and work with that output. That way you are not looping over the whole data set- just those that match at least one of your words. A major savings!

Neil Harrington · May 2022

Thanks for the feedback.
Due to what the rest of my process was doing, I ended up:
1. Creating a function to do searching as I thought I'd use it multiple times. It takes a search string to look in, and an array of strings to search; returns true if all of the array strings are in the search string; false if not.
2. Doing 1 query all to get all possible records
3. Looping thru each record, passing the record to the function, and if it returns true, adding to an array.
4. Use the array for the rest of the process.

Works okay -- not as speedy as I'd hope when that query all gets a bunch of records.

Sean Montgomery · May 2022

The PostgreSQL database that Xano uses has amazing fuzzy search support... We just haven't leveraged it yet.

It is on our radar, but we don't have an ETA yet. Probably within 2 months. I know not the best answer if you really need better support right now, but wanted to be transparent on timing.

Neil Harrington · May 2022

Neat. I look forward to playing with that when you get it working.

Jordan Scott · May 2022

Ditto that. Will be great to play around with as soon as it's working as I need a more robust search solution as well.

I'm currently just stringing together a bunch of OR statements with Includes. That works fine provided the search input matches EXACTLY, but obviously need to accommodate multiple words, mis-spellings, closely-matched integers and the like.

Hoping that 2-month timeline holds! 😉