SQL Cross-Join, then Left Join with Count

Need help Database
1

Ok, my head is hurting. I’m likely overthinking this and I can learn from this experience. I’ve read @NigelG treatise on Joins and I’m still missing how to do this.

I’ve created a diagram of what I need to do. Essentially, I need to do a SQL cross-join initially to get all possible combinations between User and Status. Then, I take the cross-join table and left join it to the status reports table so that I can get a count of how many status reports there are for each status.

Why do it this way? I need to know when there are no values so that the ChartJS plugin can chart this correctly.

I can’t go directly against the Status Reports table as it only tells me what’s there, not what’s missing.

Here’s a terrible diagram with examples. Any help is sincerely appreciated. Also, got a better idea? I’d love to hear it.

image
image822×886 78.7 KB

2

Wow :crazy_face:

3

Thinking out loud here’s the road I’d explore if I was trying to do this…

From a Bubble perspective, you’re looking to create a set of results that show every User and every possible Status. Usually people are looking to populate a repeating group (RG) with results, but I’m guessing you need to feed it into a plugin (I don’t use the plugin you mention so I don’t know).

But if it was a RG, then the info shown for each row would be User, Status and a Count for the # of reports that match that User-Status combination.

I’d probably set up a joining datatype that holds every User/Status combination. You’d populate this table by having a workflow that creates/deletes the relevant entries every time you add or delete a User.

Then you can load that data into your RG and that takes care of showing all the User-Status combinations.

Then for the count, in the RG you could just use Bubble’s count function to search for the # of reports for each combination.

There may be smarter ways to do this using Bubble’s grouping functionality, but this is the initial approach that hit me.

1 Like
4

Agreed, yeah.

Although I might them combine that with the Status Report datatype so you can merge the two.

So set up “blank” status reports for all combinations and then as the status reports are created then you add the real one.

Only “real” ones are displayed to the user.

And then do something horrid like count of status reports - 1 :flushed: :flushed: :flushed:

1 Like
5

Tricky on Bubble in a single query. In fact I would say almost impossible. Just because there is no reliable intermediate datatype for the joined tables. You could do something nasty with texts and the UID … but that will get complicated fast.

These intermediate results that SQL feel hard to do in the front end. Previous posts about this (usualy on listing product variants) almost always land on scheduling workflows e.g. User that then schedule another workflow e.g. Status

So the cross join is probably best done a) in advance b) in a backend workflow.