Join tables with nested select count and group by in Nhibernate HQL

Question

I have post, vote and comment table. Each post can have N votes and N comments. I have been trying to find a way to do this query using Nhibernate HQL with no success.

 SELECT P.Id, P.Title, P.TextDescription, ISNULL(V.TotalVotes,0), ISNULL(C.TotalComments, 0)
 FROM
 Post P

 LEFT JOIN
   (SELECT 
    PostId, count(PostId) as TotalVotes 
    FROM Vote 
    GROUP BY PostId) V
 ON V.PostId = P.Id 

 LEFT JOIN 
    (SELECT 
     PostId, count(PostId) as TotalComments
     FROM Comment 
     GROUP BY PostId) C
 ON C.PostId = P.id 

I pushed GROUP BY aggregations into nested SELECT statements because i want to group only PostId and not all those other columns. My Domain classes:

Post - properties:

int Id  { get; set; }
string Title { get; set; }
string TextDescription { get; set; }
IList<Comment> Comments { get; set; } -> HasMany
IList<Vote> Votes { get; set; } -> HasMany

Comment - properties:

int Id  { get; set; }
Post Post { get; set; } -> reference

Vote

int Id  { get; set; }
Post Post { get; set; } -> reference

I'm really puzzled about this. I hope i'm not going in the wrong direction. Maybe i should just use the Nhibernate formula attribute in which i can declare an arbitrary SQL expression for my count's.

Any help would be very much appreciated..

Thanks!

Answer 1

If you map your collections with lazy="extra", their Count property will not cause a load of the collection.

Answer 2

For what you are trying to do, you do need to write any HQL query Since you have collections in your classes and you have mapped them in your .hbm.xml files like (if you are using fluent nhibernate ignore this)

<bag name="Comments" inverse="true" lazy="extra">
    <key column="CommentId" />
    <one-to-many class="Comment,mylib" />
</bag>

<bag name="Votes" inverse="true" lazy="extra">
    <key column="VoteId" />
    <one-to-many class="Vote,mylib" />
</bag>

when you call

Post post = ISession.Get<Post>(postId);

the Comments and Votes collections are initialized with a proxy. When you touch the collection as such

post.Comments.Count

NHibernate will fire a select count(*) on the Comments with a PostId of postId.

If you absolutely want to implement this with a formula (because you want the see the count each and everytime you fetch a Post) again you will not need an HQL (or Criteria for that matter) query

EDIT: Since you do not want formulas and you want to fetch the result in a single round trip, here is a solution in HQL, (the above collection mapping is required)

IQuery query = nhSession.CreateQuery("select p, count(p.Comments), p.(p.Votes) from Post p where p.id = :postId");
query.SetInt32("postId", postId);
object result = query.UniqueResult();

where result is an array (i think ArrayList) where result[0] is a Post type object, result[1] is the comment count (int/long) and result[2] is the vote count (int/long)