Struggling with even moderately complex searches in Bubble

Questions
1

I think I might be missing something conceptually here. My DB structure is fairly standard - bunch of 1-to-many plus many-to-1 relationships. Below is a schema for part of my DB! What has me stumped is how to structure some of the queries to do even moderately complex searches!

image
image946×913 36 KB

As an example, I have been struggling with the following search - find a list of users whose total post count is <= 2 where the status for both posts is Denied (status is an option set).

I can’t seem to set conditions on a search which can count the ‘post count’ for each user to satisfy a condition. Have tried multiple ways and in each case, a search condition based on a count results in a number type, which bubble interprets as a yes/no in the final result. If I create a new field for post count, it would work but that doesn’t sound like a right solution.

I have tried advanced filters - couldn’t find a solution and filters are extremely heavy for the browser given the large amount of data.

Any help is greatly appreciated!

2

If you need to track a post count, keep a counter value on the user. That would make it simple. Increment it on post success.

It would also be very very lightweight as a search.

1 Like
3

Yes - but that won’t work when I have to search for a post count by Status. I have four options for the Status (Approved, Pending, Denied, Removed). Unless I set four data fields which count by status and a fifth one for the total - I won’t be able to search post count with an additional constraint of Status.

4

You can use a client side :filtered after the search, but that does download the whole search to the users PC.

Or you can do a search as a constraint, but that’s terrible with performance.

So yes, your best bet is multiple post count fields, at least in terms of performance.

Bubble’s downfall is intricate searches. That’s why I’m using a hybrid approach with my dating app, through Supabase.

1 Like
5

Yeah, it does seem crazy that even a modest SQL query is something which becomes insanely hard in bubble! Creating new fields seems very much like a stop gap solution - I will need to create many such queries on the transactions table where I have over 10 transaction types.

Performance is not a huge problem for such queries as these are mainly for internal dashboards - but I haven’t even been able set a filter which takes less than 10 - 20 seconds to show the result and that’s only with a few thousand rows of data - so not practical even for an internal dashboard.

I will look into Supabase!

1 Like
6

For this specific query, you could use the “group by” feature with the following setup:

Do a search for posts, filtered to status = denied, :grouped by creator, aggregated by count. Then you can filter this list to count > 2. This should not take 10-20 seconds to load, as I believe grouping/aggregation still occurs on the server. Let me know if this works!

1 Like