CODEWITHSUNDEEP
mysqlspringspring-data-jpa
JoseCarlosPB
JoseCarlosPB

Reputation: 875

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 User table

Activity table

Rule table

User Activity table

Upvotes: 0

Views: 267

Answers (1)

Nick
Nick

Reputation: 147206

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.

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.

Upvotes: 1

Related Questions