Improve Fuzzy Search and Option Sets with Database Triggers

Enhancing Fuzzy Search with Database Triggers

In this Bubble tutorial video, I'd like to demonstrate how you can use database triggers to enhance and improve the search results when using the zero-code fuzzy search plugin. I've got a very simple table going on here. It's a table of a data type called vehicles. And my vehicles are made up of two option sets. I've got make and I've got model.

Setting Up the Database and Fuzzy Search Plugin

If we have a look at my database data for vehicles, you can see here that there's just those two fields. If I go onto the design tab, I've got the search and auto correct, also known as the fuzzy search plugin, by Zeroqode added to the page. And I've linked up to the input box ID of this text input here, as you can see down here. If you need a bigger, longer explanation of how to use fuzzy search, do check out our previous videos. We cover it in a lot more detail there.

The Limitation of Option Sets in Fuzzy Search

What I'm wanting to highlight now is that I can't get the fuzzy search plugin here to pick up either of my option set fields because they are a data type of option set of the options sets I've created, make and model. Whereas fuzzy search needs to have a text field and we can use database triggers to keep that field in sync with our option sets.

Using Auto Bind for Option Sets

At the moment, I've just got these sets to auto bind and I'll demonstrate that. So let's correct this one. The Land Rover Discovery. It's autobinding and so if I refresh the page, the field is updated.

Creating a Database Trigger

Now I need to add in a database trigger and to do that, I go to backend workflows and custom, oh, here we go, general rather, new database trigger event. And so I'll say update vehicle make and model field. I want this triggered each time a change occurs to a vehicle.

Optimizing the Workflow

I'm going to build in as I'm going along a way of optimizing the workflow here, bearing in mind the cost of workload units. So I'm going to say when vehicle before changes make is not vehicle now make that make. Or when vehicle before changes model is not vehicle now's model. Without having this only when statement here, the workflow would run every single time any change is made to any field on vehicle. And I want to limit that down, of course, because I don't want to spend unnecessary workflow units.

Creating a New Text Field

So now what I can do is I can say make changes to a thing, make changes to the vehicle now. So with the more recent, the up to date data, and I can create a new field of type text. And so this will be make and model. And I like to have a bit of a convention of putting in text at the end. It's a prompt to me, remind me why I have this additional field when I'm already saving make and model. It's so I can access make and model in the fuzzy search.

Updating the Text Field

So this is of type text. And so I can say vehicle now's make display. Click slightly after add in a space vehicle now's model display. And I'm going to add in a label into my into my table so we can see this updating. All right vehicles, there we go. Let's update that. Okay and the label is empty because I've not supplied that data. So let's just make some changes. Okay, and so now we have that text label updated. So that includes the text values of the display labels for our option sets.

Configuring Fuzzy Search

And then this means that we can tap into it with the fuzzy search. And then if I just update the data source for the table. No, not do a search for that's what it was before. I still I want this to be my search and auto correct matches. Okay, so there's no results. But if I start typing in Land Rover. Now I need to tune. This is something that you need to update on your app. The reason that it's a little bit odd is these values here need to be improved depending on your use case. The threshold, the location, distance, etc. But you can at least see that it is showing the number one spot as Land Rover. Because I've typed in Land Rover.

Improving Search Results

Just a quick thing that I would do to improve this would be to say you do make this do a search for vehicles showing all vehicles. We could then basically sort them by a new field. And then I'd say conditional value is when the search matches first item is not empty. That's when I change the data source to the plugins results. So that should allow me to have all of my results appearing. But then when I start to search for Land Rover. I'm getting the search results.

Conclusion

So there you have it. That is another demonstration of how database triggers can take a burden off you of a field that you need. That you need to ensure is always in sync with two or more other fields. If you've got any questions about database triggers, leave a comment below. If you need help, if you Bubble up, head to our website. We do one-to-one tuition. That is called Bubble Coaching.