CODEWITHSUNDEEP
nhibernatehql
Dofs
Dofs

Reputation: 19247

HQL and grouping

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?

Upvotes: 1

Views: 1806

Answers (1)

Diego Mijelshon
Diego Mijelshon

Reputation: 52735

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.

Upvotes: 2

Related Questions