I’m struggling to create a complex search expression.
I have:
Users table (default one with Credits value per each user)
Subscriptions table (subscriptions with Credits per value, may be more per user, has Users linked column)
What I want to do - track users where subscriptions_credits:each item’s sum (of specific user) <> users_credits (of specific user)
I use users_credits as a fast-access value to instant display to user and do some front-end logic, while subscriptions_credits - more specific, longer to proceed, for backend workflows and more accuracy.
My problem:
Do a search for Users:
constraint: credits <> Do a search for Subscriptions:
constraint: user = …
I can’t filter a row of user. Current user won’t make it - because it will return, well, the current user only and compare all users with his subs credits.
SQL would probably look like this:
SELECT u.user_id, u.users_credits
FROM Users u
WHERE u.users_credits <> (
SELECT SUM(s.subscriptions_credits)
FROM Subscriptions s
WHERE s.user_id = u.user_id
);
Sadly this is not trivial on Bubble, but there are many workarounds which work great.
In theory you could use Advanced FIlters to quickly get this query done. But advanced filters are only useful for low volume queries, as they are done client-side and the main search would return all users. In this case Advanced Filters are definitely not the way to go.
I would personally create a field containing the the updated value for how many Credits are being used on subscriptions per User and keep that field updated in the backend.
The search query would then become much simpler, where you just compare the user credits to this new field, and no extra steps are needed.
Nested searches are generally something you always want to avoid.
If you have never used an advanced filter before, you will find the Advanced… option at the very bottom of the dropdown when you add a constraint to the filter.
