I have the same issue – but with list-to-list searches on many-to-many relationships. Like… does this list of things (in a list field) contain the items in this other list of things (items selected by a searcher). An example is… does this blog’s tags contain the tags the user has selected in search constraints.
My plan is to create a 1:1 joining table to get around this and then experiment with the most performant way of integrating the joining table search with regular searches on the datatype (since users will be putting in multiple types of search constraints).
Note though that I haven’t needed advanced filters for 1-to-many searches like you describe (I run them server side via the regular “search for” box), so maybe I’m not understanding your example.
PS: I think advanced filters should come with a big warning in the editor that “this isn’t scalable, so your app will slow down as the database grows”. Advanced filters have some needed functionality, but it’s a band-aid approach and ideally Bubble does some development that allows us to do more types of searches server side.
@ed727 I think I may be missing something since a couple people have now mentioned running the search server side.
My understanding of how this is accomplished is by having a Workflow API and using the API Plugin to use it as a data source (because for whatever reason you can only schedule an API call on the front-end not use it as a data source ).
My problem with the plugin is that there’s no way to declare that the data returned is a list of “things” that are bubble objects.
Hi, you are correct that to run an advanced filter “server side” would require the method you are describing.
However, a one-to-many search doesn’t require an advanced filter. In your case, your “Basket” datatype can have a “Food” field which is a list field that connects to the “Food” datatype. So each “Basket” thing can connect to a list of “Food” things.
If you are set up this way, then you can run this search in the regular “Do a search for” box (which is server side), as long as the search is only for one Food at a time, like searching for Baskets which contain Apple. But if you wanted to search for Baskets that contain Apple and Banana, then you’re at a list-to-list search, which requires an advanced filter or joining table.
But I’m guessing that your Basket may have Food items in it, but also has other things like quantity and date added. In which case you’ll have built a table to hold that data, which also could serve as the joining table. If this is the case, you could search that Basket-Food table through a simple server side search (no advanced filter).
You’ve more or less got the gyst but the Basket and Food example is just a simplified version of what I’ve got.
The problem is that the Basket also has other things in it (Drink, Clothing, etc), and the returned list needs to be individual Baskets. So the core search needs to stem from Baskets and filter the associated objects.
@rpetribu Foods isn’t an option set it’s another table. so list doesn’t contain options but a list of things there food.type = apple or banana
From my understanding of how everything works, there are a couple of options that bubble could implement that shouldn’t (my guess?) be too difficult.
A - Let the API plugin identify returned data as being a bubble-native object type
B - Let Advanced Filters be applied on the initial constraints of “Do a search for” (and simply just have it work the same as a backend workflow that uses :filtered)?
I wonder if you could collapse Drink, Clothing, etc. into one “Product” datatype, and then you only need one joining table.
Otherwise you’d have to have multiple joining tables (Basket-Drink, Basket-Clothing, Basket-Food), and then run searches for Baskets on each of those tables and then either merge or intersect those searches.
Those searches will return lists of baskets (and you can filter out dupes using the “unique” operator). I think this should run server side, but it’s hard to be sure without building it and then checking Chrome devtools to see what Bubble is actually loading to the page.
In any case, this is the direction I’m planning to go when I can find the time. The approach is more similar to what you’d do in a traditional database that doesn’t allow list fields.
Maintaining those association tables is probably a mess to deal with.
You would have to populate them with all the basket and food data you would want to filter on otherwise you run into the same issue (not being able to filter related objects).
My take is that the only solid use case for association tables is just when you want to have associations as sources of information. For example replace Basket with Cashier and see how you assign and unassign foods to the cashier for checkouts. Otherwise, they just increase overhead with minimal benefit.
Generally, I’m not a fan of having to set things up in a way that solves for 1 issue but potentially opens you up to more down the road. The data layer should be agnostic of business logic and given this filtering limitation that’s very hard to accomplish
The issue is that this still just works with Foods that are equivalent to option sets. Even though they’re split out into a table, there’s only one Banana instance. We need an instance for every Banana out there (where each instance will have its own serial number).
Edit… realizing that option sets don’t give you the opportunity to grab a unqiue ID… so I supposed I’d need to create a new field in the option set that has the phrase with hyphens, to prevent multi-word options pulling up other multi-word options that contain some of the same words.
I played with it by creating a new field in the option set that contained the display words without spaces, effectively creating a unique word that could be used for searching. And in my datatype entries, I created a text field that could be populated by unique words from the option set selections.
Searching worked, but it needs to be “contains keywords” since “contains” was looking for words to be adjacent to each other.
However, this is an “AND” search, meaning that all the selections need to be in an entry for it to display. I can’t see a way to do an “OR” search (ie show me results that contain ANY of the selections) via this method.