Note: This is also posted on Stackoverflow at
http://stackoverflow.com/questions/666055/how-to-query-for-most-commonly-used-many-to-one-in-nhibernate
but have been unable to get a working answer.
I have the following issue in the project I am working on. Each
transaction in the system is assigned to a given user. So there is a
many to one relationship between transactions and users, like so:
public class User
{
public int ID { get; private set; }
public string FirstName { get; set; }
....
}
public class Transaction
{
public int ID { get; private set; }
public User CreatedBy { get; private set; }
...
}
I have mapped these entities with NHibernate so that there is a many-
to-one mapping between the Transaction and the User classes. The User
object doesn't have a list of transactions, but the Transaction has a
reference to the User that created it.
Now I want to query to retrieve a list of the users who created the
most transactions, but I can't figure out how to get the top 10 most
referenced users using NHibernate.
Any ideas? I would like to be able to use ICriteria to complete this
rather than HQL, but HQL would be ok if required.
Update
I tried sirrocco's suggestion with the query as...
DetachedCriteria topReferencedUsers = DetatchedCriteria.For(typeof
(Transaction))
.SetProjection(Projections.GroupProperty("CreatedBy.Id"))
.SetProjection(Projections.Count("CreatedBy.Id").As("pcount" ))
.AddOrder(Order.Desc("pcount"))
.SetMaxResults(10);
and build that as the subquery...
GetSession().CreateCriteria(typeof (User))
.Add(Subqueries.PropertyIn("Id", topReferencedUsers))
.List<User>();
but this subquery does not group but returns the total number of
transactions, which are then used as the IN clause to the User query.
If I add the ProjectionList() with both projections, I get the output
of the subquery that I want, but it fails because it tries to run the
two column output into the IN clause of the User query. How do I get
NHibernate to project both the ID and the Count, but only join on the
ID?
Update (2)
I tried the suggestion to use a SqlGroupProjection as:
DetachedCriteria topReferencedUsers = DetatchedCriteria.For(typeof
(Transaction))
.SetProjection(Projections.SqlGroupProperty("count(CreatedById) as
pcount",
"CreatedById", new [] { "pcount" }, NHibernateUtil.Int32 ))
.AddOrder(Order.Desc("pcount"))
.SetMaxResults(10);
This came up empty. First it gave me errors saying that it couldn't
find the property pcount, which meant that I needed to remove the
order by, which means it was ordering by some timestamp, which won't
work. But even with that, it is still only outputing the count of the
times that the user was referenced, not the user id with which to join
the Users table. Any ideas? Thanks.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"nhusers" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/nhusers?hl=en
-~----------~----~----~----~------~----~------~--~---
