CODEWITHSUNDEEP
nhibernate
Chris Conway
Chris Conway

Reputation: 16519

Group By with Count using NHibernate

Given the following tables:

Resources:
ID int,
Name varchar(100),
Address varchar(500),
City varchar(100),
etc.

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

every time a resource is looked at, an entry is added to the ResourceView table for that user.

Here are the corresponding 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 top 5 most viewed resources in order by the count similar to what the following sql retrieves:

select * from [resource]
where ID in (
select top 5 resourceId from resourceViews
where userId = 3
group by (resourceId)
order by count(*) desc
)

Bonus points if it can be done with Criteria API instead of hql.

Upvotes: 4

Views: 5457

Answers (3)

ValidfroM
ValidfroM

Reputation: 2837

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

To help someone who tried to find 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();

Upvotes: 2

Chris Conway
Chris Conway

Reputation: 16519

tolism7 had it 99% of the way there, here's the final solution for anyone 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>();

Upvotes: 2

amavroudakis
amavroudakis

Reputation: 2362

Try this:

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 on your question. Hence I believe it is what you are looking for.

Upvotes: 5

Related Questions