Forum Academy Marketplace Showcase Pricing Features

Many-to-many searches on large datasets?

I’m hoping there’s an experienced Bubbler who can give some guidance on best practices for many-to-many searches (or, searching a list based on a list) involving large datasets. An example would be searching for Articles using multiple Subject tags.

The method commonly discussed on the forum is to do an advanced filter and intersect with. This works, but for larger data sets it’s problematic because it’s executed client side. For a large dataset, Bubble loads thousands of entries into the browser to get enough matches to fill the repeating group. The browser then lags and can even crash.

Because of the above limitation, there are two additional methods I’m exploring:

  1. Moving the search to a backend workflow with a “Return Data from API” action. Bubble lets you build the search constraints in the backend workflow, including using the advanced filter and “intersect with”. You then use the API connector to pass the search parameters and get the results, as though you are calling an external data source. Bubble limits results to 50 items.

  2. Using an intersecting/joining table, where you create an entry for each Article-Subject connection. Then, in your search results RG, you set it up as:

  • The Articles-Subjects joining table is the data source and you use “:unique elements” to remove duplicates (since an Article may appear multiple times in results, once for each matching Subject tag).

  • Or you use Article as the data source. You do a nested search to look for Articles that appear in a search for Articles in the Article-Subject joining table which contain the selected Subject tags.

On a basic level I know how to set all of the above up. But I expect that as I move into implementation I will run into snags here and there.

Have any Bubblers set up the above (or another method) to do many-to-many searches server side, instead of the client side advanced filter?

And if so, can you offer any advice on the best method that combines performance, scalability and simplicity?

Many thanks.

@ed727

Save the tags as text in a text field of the article as they are created. This way you avoid having a list and searches become faster with no filtering needed :wink:

Thank you for taking a look. I need to keep my tags as their own datatype or option set for a number of reasons, including my parent/child structure, being able to flow through name changes, and ease of having them auto populate in search options.

That said, even if I were to go to a text field with a bunch of tags in it, a basic search wouldn’t work in my case, since users want to see any results which contain at least 1 of the tags selected.

Which leads me back to the many-to-many search…

@ed727

My suggestion did not mean for you to do without tags

It is about saving them all as a text in a text field of the article object. This text is re-populated every time you create/delete/update tags.

It is a technique to be able to handle quicker searching and large number of entries.

In short … using both :grinning:

Ah, I see. Thank you for the clarification. I could see a problem with this approach if words were shared between tags it could be tricky to ensure what comes back is correct. But putting that aside, I’m seeing the same issue with searching…

  • For example in my Article datatype I create a text field called TagText and save my tags… Politics, Public Policy, Economics, Regulation.
  • A user selects Politics and Regulation to search. If the search is an “And” search (both tags must be in the article’s TagText field), then I can see how it would work. You could do a search constraint like… Article’s TagText contains keywords… and have those keywords be the selected tags converted to text.
  • But if you need an “Or” search (return all Articles with Politics as a tag or Regulation as a tag), then I don’t see how you could do that with a normal search constraint.

@ed727

As with most things in programming… there are ways and there are also compromises.

Conditionality could be used to display search data using contains keyword when needed, or just contains, or any field, or is… etc etc.

Hope things work out :+1:t2: