how can I return multiple row counts

I have three entities, User, Rule, and Activity

with the following relationship, One rule has many activities (bidirectional)
Many users have many activities(bidirectional), the server is with spring using spring jpa

I want to show how many Activities has one user started but not finished from every rule, with SQL this query would return that from one rule

SELECT COUNT(*) FROM user_activity WHERE user_id=userId AND score<10 AND
activity_id IN (SELECT id FROM activity WHERE rule_id=ruleId);


I could do that with JPQL, but how could I return that count from every rule?
so I could have from every rule, how many activities has that User

if someone needs it I can provide java model code too.

tables are User, Activity, Rule and User_Activity

1 Answer
1

I think this query is probably what you want. It finds all the activities associated with a given rule, then joins the users participating in that activity who have not finished it (I presume that's what score<10 indicates?)

SELECT r.id, u.id, COUNT(a.id)
FROM rule r
JOIN activity a ON a.rule_id = r.id
JOIN user_activity ua ON ua.activity_id = a.id AND ua.score < 10
JOIN user u ON u.id = ua.user_id
GROUP BY r.id, u.id


Obviously you can expand the SELECT to include things like the rule description and the user name etc.

SELECT

I couldn't see any indication of what "activity complete" means so I've presumed it's that the score is less than 10. If that's not the case, you would need to change the join to user_activity appropriately.

user_activity

Thanks for contributing an answer to Stack Overflow!

But avoid …

To learn more, see our tips on writing great answers.

Required, but never shown

Required, but never shown

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.