I want to filter a list from a list, lemme explain a scenario. Let suppose I have a table “User” and the field is a list of option set “A”.
I have a repeating group in which I’m displaying option set A with check boxes so when user marks the checkbox it will add that option set value into the index page state which is also the list of option set A.
Depending on state I have to filter the results of user table.
One case is Do a search for users and constraint is option set A field contains index:optionsetA:item#1 or any other item, with this I can filter with AND logic but how do I apply filter with OR logic ? I don’t want to use advanced filter because it’s quite slow
I posted the same question and am hoping that in the Bubbleverse someone has implemented a solution and can share their guidance.
To search a list based on a list, there’s the advanced filter and intersect with function. It runs client side and as you point out, that is slow on large datasets.
I’m aware of two methods to do this search server side:
- Run the advanced filter as a backend worfkow and get the results (up to 50) using the API connector. The advanced filter should run server side in this case.
- Create a joining table to hold the connections, and search using that
I am leaning towards option #2, but am hoping someone has done one or both of the above (or another solution) and can offer some advice on how it works with large datasets and any considerations to be aware of.
1 Like
Thanks, I got the first one but little bit confused about 2nd one. Can you elaborate it further which fields should I put in joining table and then what would be the filter wrt the scenario I mentioned above.
Hi, a joining table would be a simple table (or “datatype” in Bubblespeak) that connects each User to each Option. They are pretty common in traditional databases, which don’t have list fields so you have to create a table to establish the many-to-many relationship.
In your case, the only two fields would be “User” (referencing a single User in your User table) and “Option” (referencing a single Option in your Option table or Option Set). If a User had four Options assigned to it, then you’d need four entries, one for each User-Option connection.
Then you can do a list to list search either by:
A) Using the User-Option table as the data source for your RG. But you’ll have to use “Unique elements” to remove duplicates, since if a User matched multiple Options that were selected for search, the User would show up multiple times.
B) Using the User table as the RG data source. You’ll then need to nest a search to say, search the User-Option table for the Options selected, and is the User in those results.
I was playing around with this in my sandbox app, where I have “Owners” and “Pets”. Each Owner has several Pets, and each Pet is shared among many Owners. I want to filter to find Owners that contain any of the Pets selected in a multidropdown. Shots below…
Here is the joining table:
Method A search:
Method B search:
And the “Search for Owner/Pets Joinings’s Owner’s Name” is the nested search below:
Based on what I know, all the methods I’ve discussed will “work” – but the question is which will work best for a large set of data? My gut tells me to go with the joining table and Method B, but I’ll only find out once I set it up.
This brings me back to my original question – many-to-many relationships and list-to-list searches are not uncommon, and so I have to believe that someone among the thousands of Bubble users has had to move away from the advanced filter and intersect with to do a list-to-list search, and so what did they do and what are best practices?
3 Likes
Got you. Thank you very much for the detailed guidance.
This topic was automatically closed after 70 days. New replies are no longer allowed.