Nested searches and database design - right way?

I’ve run into an issue that is forcing me decide between correct database design vs. faster searches.

Good database design/normalization calls for eliminating redundancy. This is done by creating separate tables for each data type and establishing relationships between those tables.

For example, you have a table of Conferences. Instead of including the location of each Conference in that table, you create a Venue table that contains the location’s name, address, city and country. Then within the Conferences table, you have a field that links to a Venue. So far so good… each Conference is linked to a Venue. And when you display that Conference to the user, you can display the Venue’s fields as well.

The issue comes with searching. You’ll want your users to be able to search Conferences by city and country. However, those fields are contained within the Venues table, not the Conferences table. When selecting the search constraints for a RG of Conferences, Bubble doesn’t give “Venue’s City” or “Venue’s Country” as options. You just get “Venue”. So, you have to create a nested search (which looks like… “Venues = Do a search for…”)., where you create a search for “Venues” that match up with your city and country inputs.

Technically this works, but it’s very slow. I believe it’s because Bubble first searches for all the Venues that match the City and Country inputs, and then searches all the conferences that match the results of the Venues search. In addition to the search being too slow from a user perspective, I’m also concerned that it’s chewing through a lot of capacity.

The only solution I can think of would be to have “City” and “Country” fields in the Conferences table in addition to the Venues field. The would eliminate the need for a nested search, since they could be searched directly. This would speed things up, but it goes against everything that efficient database design calls for.

Am I missing any solution?

3 Likes

Hello, I should add that the advanced filter function does allow for selecting the Venue’s city directly:

image

However my dataset is large and advanced filters are slow.

I’m afraid that the answer here is that Bubble does not provide a fast/efficient way of executing nested searches, but just trying to confirm.

Thank you in advance.

I am facing this issue as well. A workaround I had in mind was to have a copy of the fields from the nested data type in the parent thing just to improve searching. This would be strictly for searching.

Dodgy workaround on first dB object creation but that would break the parent autobinding flow if someone goes and does an edit. Any edit workflow would need to have an update step.

This could work on initial setup but I already have a lot of listing data so would like a nice way forward on this if possible.

Regards
ZubairLK

Yes, that’s the workaround I’m considering as well – having those child fields be in the parent as well. As you say, that then creates other problems related to keeping those parent and child fields aligned. It also goes against correct database design. I too have a lot of entries, so I’d need to do some sort of batch updating across lots of entries, and then go back and quality check them to ensure it worked.

I have run into this same limitation, but in my case, I am comparing one or more lists that a user creates dynamically to lists that have been saved on things in a data type. I have put a solid week into trying to overcome this limitation (including scouring every word I can find on the topic here in the forum, in Bubble’s documentation, or anywhere on the internet for that matter), and I simply cannot find a way to to compare two lists without using an advanced filter, and the system just can’t handle it (and I don’t even have that many entries at this point). True, this particular project is only the Hobby plan, but even with a boost, the system still times out on the workflow in question.

Anyway, I have come to the conclusion that the only way to overcome this limitation in my case is by redesigning the user experience, and that, quite frankly, stinks. But it is what it is, I guess, and I definitely understand what you guys are running into here.

Best…
Mike

Mike, I have a similar issue, which for me is a separate issue where I’m trying to filter a list of entries based on a list of subject tags. Each entry has multiple subject tags, which are set up as an option set. For now I’m using an advanced filter and “intersect with”, which I learned of here: Searching using multi dropdown - possible/alternative? Incompatible type.

On my initial testing it’s working fast enough to live with it – but my search is pretty simple in that it’s just filtering a list of entries based on subject tags. I have the results set up as a scrolling repeating group, so Bubble only has to find enough to fill the first few entries. I’ll likely also have to put a note for the user that if they are searching on subject tags, to be patient. I’m on a professional plan - not sure if that makes a difference.

I had asked Bubble support whether there was a way to do this “searching a list based on list” search client side, which should be faster, and they said no.

Ah, I remember that thread well (the one you linked to)… I responded in it five times. :slight_smile:

It’s discouraging that you are on the professional plan, and you still can’t get reasonable results with an advanced filter. It’s also disappointing to hear that you are going to go the user experience route, too, by asking your users to be patient. Those things combined with Bubble’s response to your question have me convinced that changing the user experience is the only way I can go, and so, I will go that way.

Thanks for the additional info, @ed727… I really appreciate it.

Best…
Mike

1 Like

Mike, thank you… when I referenced that thread I hadn’t realized you were one of the main contributors! Figuring out that search functionality was critical to what I’m working on.

1 Like

i have solved this by using 3 calculatiors

calculator 1:
visible on page
exp is 1
condition: list 1 contains list list2: if yes, exp is 0 and not visible
calculator 2:
visible on page
exp is 1
condition: list 2 contains list list 1: if yes, exp is 0 and not visible

calculator 3
not vis on page load
exp: 0
condition: when calc1+calc2 res>0, visible, exp: 1

workflow: when calc 3 result is ready and the res is 1, do the desired thing.

works inside of repeating groups too.

another way is to compare text strings.

arb text (list of things each ones UI) is not arb text (list of things each ones UI), if yes calc becomes vis and does the thing

Does anyone solved this “equation”?
I have the same problem.