Counting subgroups. Is this the most efficient way?

Hello, I have implemented a way of counting favourites but I have a nagging feeling it’s not a very efficient way to do it. Can someone please let me know if this is the best method?

  1. There are users, and songs.

  2. There is a favourites table. This simply links a user and a song.

  3. A user has a ‘career level’ field which is either hobbyist, professional or VIP.

  4. In the Admin panel we want to be able to look at a song, and see how many times it’s been favourited This is really simple: Search For Favourites: song=parent groups song: count. no issues there.

  5. We need to break this down into ‘career level’ groups. Hobbiest, Professionals, VIPs. with each displaying the count. For example:

Song: Kiss the sun
Favourited: 20 times.

  • Hobbiests: 10
  • Pros: 8
  • VIPs 2.

Makes sense? ok cool… this is the search i’m using to grab those group/breakdown figures:

Do a search for Favourites: song=parent groups song: filtered:advance filter: This users career_level is pro :count

I’ve heard using advanced filters and list filters is not good practice and can be inefficient, so is this the best and only way to achieve this? Or have I overcook it?

Many thanks
Dale

ICYMI: See the :group by operator.

To achieve the desired results, you might need to add a career_level field to the favourites data type for ease of reference. If a user’s career_level changes, you could either update or not their related favourites entries.

Thank you David. We did consider this, but it does not feel like a good trade of in terms of costs and efficiency. For example, if we’re having hundreds/thousands of favourites each day, that is a LOT more database writes, and as you mentioned, those static values could change and get out of date, requiring regular updates. Seems like i’d just be swapping one imperfect method for another. I appreciate the idea though. Thank you.

AFAIK, there’s no or trivial marginal workload units associated with populating an additional field when you’re creating the favourites entry anyway.

Often the case in Bubble (and other platforms).

Others in the community who have actually dealt with your use case may have more valuable guidance.

I think you can access columns of linked types once you have the main type found.

Inside a rg cell, you should be able to display a text:

Curent cell song > users > each items level is [whatever] count

most efficient would be to store the career type on the favorite

otherwise bubble would fetch each users data to then filter it for the advanced filter which would be very inefficient.

essentially
career on favorites (search by favorite: group by career type) = result is billed as group by (search for: group by) ie 0.3wu
career on user then (search by favorite, each items user :grouped by career type) = result is list of songs, list of users, aggregated on front end ie search 0.3 wu + data cost + search 0.3 wu + data cost = a lot more WUs and significant load for the user since the processing is done client side

the best way to do it is to write the career type on the favorite

setup a database trigger to keep it updated (higher wu cost but easier)
or setup an api workflow and call it whenever the field is updated (slightly higher wu cost for each workflow, but lower overall wu cost since it only evaluates when the field changes not when ANY field on the user changes. (better method imho)

Thank you guys, it looks like that is the way to go then. :sun_with_face: