NHibernate How to Select distinct objects based on specific property using HQL?

Question

How can HQL be used to select specific objects that meet a certain criteria?

We've tried the following to generate a list of top ten subscribed RSS feeds (where SubscriptionCount is a derived property):

var topTen = UoW.Session.CreateQuery( @"SELECT distinct rss 
                                                 FROM RssFeedSubscription rss
                                                 group by rss.FeedUrl
                                                 order by rss.SubscriptionCount DESC
                                                 ")
.SetMaxResults(10)
.List<RssFeedSubscription>();

Where the intention is only to select the two unique feed URLs in the database, rather than the ten rows int the database instantiated as objects. The result of the above is:

Column 'RssSubscriptions.Id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

It's possible just to thin out the results so that we take out the two unique feed URLs after we get the data back from the database, but there must be a way to do this at the DB level using HQL?

EDIT: We realise it's possible to do a Scalar query and then manually pull out values, but is there not a way of simply specifying a match criteria for objects pulled back?

Answer 1

It's the group by rss.FeedUrl that's causing you the problem. It doesn't look like you need it since you're selecting the entities themselves. Remove that and I think you'll be good.

EDIT - My apologies I didn't notice the part about the "derived property". By that I assume you mean it's not a Hibernate-mapped property and, thus doesn't actually have a column in the table? That would explain the second error message you received in your query. You may need to remove the "order by" clause as well and do your sorting in Java if that's the case.

Answer 2

You need to perform a scalar query. Here is an example from the NHibernate docs:

IEnumerable results = sess.Enumerable(
    "select cat.Color, min(cat.Birthdate), count(cat) from Cat cat " +
    "group by cat.Color"
);
foreach ( object[] row in results )
{
    Color type = (Color) row[0];
    DateTime oldest = (DateTime) row[1];
    int count = (int) row[2];
    .....
}

Answer 3

If you change your HQL a bit to look like that:

var topTen = UoW.Session.CreateQuery( @"SELECT distinct rss.FeedUrl
                                          FROM RssFeedSubscription rss
                                          group by rss.FeedUrl
                                          order by rss.SubscriptionCount DESC
                                        ")
.SetMaxResults(10)
.List();

the topTen variable will be an object[] with 2 elements in there being the 2 feed URLs.

You can have this returned as strongly typed collection if you use the SetResultTransformer() method of the IQuery interfase.