Hello! I am working on a somewhat nuanced and tricky database query and wondering (a) if it’s possible in Bubble, and (b) how to approach it.
Structure:
- Users have many “Songs” (via a user-song join table).
- Users can create a “Playlist” which can also contain many songs (via a setlist-song join table)
- Each “Playlist” has a list of Users (let’s call this list the “Contributors”) that are able to contribute their “Songs” to it. “Contributors” are stored in a “Thing, is a List” field of the “Playlist” (althoguh I am not married to this schema)
- “Contributors” owned songs are added, up to a “Max Songs” limit, based on their subscription tier.
I want any “Contributor” to be able to look at a given “Playlist” and see the full aggregated list of “Songs” from each user with consideration for their respective “Max Songs” limit. End result is a list that encompasses all the songs across all the Editors, BUT limit each “Contributor’s” list of songs individually by their own “Max Songs” permission.
ie:
Sally has 5 songs, with a max songs permission of 10
Bob has 15 songs, with a max songs permission of 10
Jimbo has 22 songs, with a max songs permission of 20
Mary has 27 songs, with a max songs permission of 30
The resulting list would return 5 songs from Sally, 10 songs from Bob, 20 songs from Jimbo, and 27 songs from Mary. All in the same list.
… this query would be easy were it not for the “Max Songs” limit… but this is really important to my application. I basically need to loop through the “Contributor” list and “Do a search for…” for each… is this possible? Is there a more efficient way?
Help! Thanks in advance!