Group by graph using NHibernate

Question

Given the following tables:

Resources
ID int
Title varchar (100),
Address Varchar (500),
City varchar (100),
and etc.

ResourceViews:
Id int,
resourceId int,
userId int,
viewDate DateTime

every time a resource is viewed, a record is added to the ResourceView table for this user.

Here are the relevant classes:

public class Resource { public int Id { get; set; } public string Name { get; set; } public string Address { get; set; } public string City { get; set; } public IList<ResourceView> ResourceViews { get; set; } // simplified etc. -- class simplified for question } public class ResourceView { public int Id { get; set; } public Resource Resource { get; set; } public User User { get; set; } public DateTime ViewDate { get; set; } } 

Using NHibernate, how can I get the 5 most popular resources in order in a row, similar to what the following sql gets:

select * from [resource]
where id in (
select top 5 resourceId from resourceViews
where userId = 3
group by (resourceId)
Sort by number (*) Descending)

Bonus points if this can be done using the API criteria instead of hql.

Answer 1

Try the following:

 DetachedCriteria dcriteria = DetachedCriteria.For<ResourceView>("rv") .Add(Restrictions.Eq("userId", 3)) .SetProjection(Projections.GroupProperty("rv.PostID")) .AddOrder(Order.Desc(Projections.Count("rv.Id"))) .SetMaxResults(5); var results = NHibernateSessionManager.Session.CreateCriteria<Resource>("r") .Add(Subqueries.PropertyIn("r.Id", dcriteria)) .List<Resource>(); 

The generated SQL looks exactly like the one you have for your question. Therefore, I believe that this is what you are looking for.

Answer 2

tolism7 had 99% of the way there, here is the final solution for someone else with a similar problem.

 var dcriteria = DetachedCriteria.For<ResourceView>("rv") .Add(Restrictions.Eq("User", user)) .SetProjection(Projections.GroupProperty("rv.Resource")) .AddOrder(Order.Desc(Projections.Count("rv.Id"))) .SetMaxResults(maxResults); var results = Session.CreateCriteria<Resource>("r") .Add(Subqueries.PropertyIn("r.Id", dcriteria)) .List<Resource>(); 

Answer 3

DetachedCriteria is one way to do this, another way that I think is more elegant is to use LINQ.

To help someone who tried to find the answer to the question in Nhibernate 3.1+ through this post, like me, I will post my answer to the question here.

I am using NHibernate3.2 in which full LINQ support is available.

using NHibernate.Linq;

  var session = Application.SessionFactory.GetCurrentSession(); var _query = from r in session.Query<Resource>() orderby r.ResourceViews.Count select r; return _query.Take(maxResults).ToList();