Hi guys,
I have a forum-like system where a user has an access group and a post can be accessed by an access group. A post is of a category type, and I'd like to make a list of the category names with the number of posts the user has written in this category in a paranthesis. A user may not have access to all his posts, because one access group can be "DELETED", where the post looks deleted but is in fact just put in the DELETED access group.
I'd like to make a list of category names with, in paranthesis, the amount of posts that user has written that he has access to. Can I do this as a HQL query? If so, how do I express this?
My try was:
SELECT category.name, count(category) FROM User user
LEFT JOIN user.posts AS post
LEFT JOIN post.category AS category
WHERE user.accessGroup IN post.accessGroups
AND user = :user
But this won't include the categories where the user has written no posts (that he still has access to).
In SQL, I'd just do a union select and group it correctly
Cheers
Nik