Advanced Filters on server side

It seems impossible to properly normalize your data due to filtering constraints on “Do a search for”.

As a simple example, let’s have two tables: Basket and Food. Relationship being Basket 1:m food.

To do something as simple as “show me all the baskets that have an apple in them” requires an incredible amount of workarounds - mainly due to the fact that you cannot implement Advanced Filtering on the server-side part of the search.

I’ve used the API Plugin to do the search on the backend and return the results, it works significantly better speed-wise but breaks the FE<>Data relationship (you’re no longer working with baskets you’re working with an object defined in the API plugin that doesn’t have most bubble features)

How do you deal with this? I’ve started going down the path of making database triggers that maintain properties on the Basket object that I might want to filter for but it’s a terrible practice.

2 Likes

This is not true. You can perform an Advanced filter after any Search during a backend workflow.

1 Like

Correct, but afaik the only way to make use of a backend workflows data in the front end is to use the API plugin, which forces you to make a new “type of thing” in the front-end which isn’t aware of bubble data anymore.

1 Like

I am not understanding what you are trying to do :grimacing:.

Because this :point_down: can be done in a “single line” in Bubble. Don’t matter if front or backend. No workarounds needed. Just do something like Search for basckets Filtered Fruits_list Contain Apple

Could you be more specific?

You are right, but there is a big difference in performance when using “Do a search for”:“filtered” and actually adding the constraint within the “Do a search for” step.

Filtered pulls all the data to the front-end and does the filtering there, the constraints within do a search for are done on the server.

So when you have 1000 baskets and want to filter for which has an apple, bubble first goes and fetches every list of foods one by one for each basket (1000 lists = 1000 queries) then does the filtering on the front-end.

4 Likes

Do it backend then :slight_smile:

Sure but you end up losing the ability to deal with the object in a way where bubble knows it’s a basket.

If you return it through the API you can’t explain that fields are relations to other things (ex: list of foods can just be a string not an actual reference to the foods)

1 Like

yes that is correct that you need triggers
or whenever you create a thing in step 1, in step 2, modify another related thing.

alternatively use an external database

Just in case you missed it, for short lists you can use the field “list of things”
eg Do a search for users where user is in current users team members (a list of users) requires no :filtered

I’ve had some bugs with triggers so I’m somewhat wary to keep getting deeper into depending on them :frowning:

Yeah but unfortunately the only filter operations you can have on the list_of_things are [=; is in; is empty] and their opposites… none of which make any sense in this case

(I need to check that food.type = apple not that food = apple )

Also, the triggers would mean that I’m having to keep any property I want to filter on sync’ed on the Basket object. It sucks for 1:1 relationships and is TERRIBLE for 1:m.

Would essentially need to make a new property called “basket_has_apple” and run somewhat expensive queries to keep that up to date everytime something changes in the basket

yep i have that in my apps. great fun.
my triggers have not had any bugs yet. what stuff have you seen? or how many changes do you make per trigger?

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.

2 Likes

What if you tried using a different table system? Like notion?
When you query on a different site you’re not using your servers resources at all.

I use my plugin Full Notion to query fast results, and I don’t believe it effects performance.

@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 :confused: ).

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.

Am I missing something?

1 Like

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).

I’m speculating though, not having seen your app.

Agree :+1:
That is why I was not understanding the problem. You don’t need the filter. You can have a list of foods inside your basket.

Actually, you can! You will have to make two constraints:

Food_list contains Banana
Food_list contains Apple

Doing this, the Search will return the baskets that contains both, Banana and Apple. No filter required.

2 Likes

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)?

@emmanuel

1 Like

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 :frowning:

2 Likes