HQL and grouping

Question

After much problems with using 'group by' in linq2nhibernate, I have tried to switch to HQL, but I am struggeling with a simple example.

I have the following table (ForumThreadRatings):

Group by

I would like to retrieve a list of the highest rated forum threads, which means I need to do a sum with the positive column and a group by the forumthread. I have tried for an example just to do a simple group by in HQL with no luck:

select ftr.ForumThread from ForumThreadRating ftr group by ftr.ForumThread

But I receive the following error:

Column 'ForumThreads.Id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

What might I be missing?

Diego Mijelshon · Accepted Answer · 2011-03-02T23:08:04.740

2

From the docs:

NHibernate currently does not expand a grouped entity, so you can't write group by cat if all properties of cat are non-aggregated. You have to list all non-aggregated properties explicitly.

In any case, that exact query can be accomplished by:

select distinct ftr.ForumThread from ForumThreadRating ftr

But of course you probably need to sum or count something, so you'll need to explicitly aggregate the properties.

Update: here's how to get the top 10 threads:

var topThreads = session.CreateQuery(@"
                 select (select sum(case
                                      when rating.Positive = true then 1
                                      else -1
                                    end)
                         from ForumThreadRating rating
                         where rating.ForumThread = thread),
                        thread
                 from ForumThread thread
                 order by 1 desc
                 ")
                 .SetMaxResults(10)
                 .List<object[]>()

As you can see, this query returns a list of object[] with two elements each: [0] is the rating and [1] is the ForumThread.

You can get just the ForumThreads using:

.Select(x => (ForumThread)x[1]);

Or project them into a DTO, etc.

edited Mar 02 '11 at 23:08

answered Mar 01 '11 at 18:18

Diego Mijelshon

51,805
14
112
150

Thanks, I have tried with "select ftr.ForumThread.Id from ForumThreadRating ftr group by ftr.ForumThread" which returns the list of IDs, but how can I retrieve the objects in the same query? I have been looking into finding something similar to a sql "Select ForumThreads where Id in (select id...)" at http://docs.jboss.org/hibernate/core/3.3/reference/en/html/queryhql.html but havn't been able to find a way to do it? – Dofs Mar 01 '11 at 19:22
@Dofs: What's the *actual* query you want to run? i.e. what is your result set? – Diego Mijelshon Mar 01 '11 at 20:51
Hi Diegeo Mijelshon, I would like to retrieve a list with the top 10 forum threads, ordered by the rating. There is an entry for each rating, which can either be positive or negative (like SO), so the ordering should be a sum of the positive ratings for a specific thread substracted by the amount of negative ratings. – Dofs Mar 02 '11 at 09:27
Wow thanks, I just wanted to have a simple example to understand it. If I could upvote you more than once, I would do it. Thanks! – Dofs Mar 02 '11 at 17:56
Just one comment for people looking at the thread, if you try to do paging like .SetFirstResult((page - 1) * pageSize) on the query it won't work. – Dofs Mar 10 '11 at 19:53

HQL and grouping

1 Answers1