NHibernate Projections and "Having" clause

Question

I'm using NHibernate to query my database with the criteria API. My criteria is below:

ICriteria c = Session.CreateCriteria(typeof(Transaction));

ProjectionList projections = Projections.ProjectionList();
projections.Add(Projections.Sum("Units"), "Units");
projections.Add(Projections.GroupProperty("Account"), "Account");
projections.Add(Projections.GroupProperty("Security"), "Security");
c.SetProjection(projections);

This is working fine, but what I would like is a way to be able to limit the query to only return when the "Units" property is > 0. In SQL I would simply us a Having Units > 0 clause however I haven't been able to find a way to do this in NHibernate. Does anyone have any ideas or is my only option to use HQL?

score 5 · Accepted Answer · edited Mar 12 '09 at 01:37

5

You can access the ProjectionCriteria from the Criteria object.

...
c.SetProjection(projections)
.ProjectionCriteria
.Add(Restrictions.Ge("Units", 0));

EDIT: This solution doesn't currently work, however it should work in NHibernate 2.1.0

edited Mar 12 '09 at 01:37

lomaxx

113,627
57
144
179

answered Mar 12 '09 at 01:00

Stuart Childs

3,295
1
19
11

2

unfortunately, this doesn't quite work how I wanted. The restriction is applied in the WHERE clause rather than in a having clause. The end result being that each individual row is restricted rather than the sum of all the rows. – lomaxx Mar 12 '09 at 01:11
Weird, I could swear I've used this before and it worked. Unfortunately, the best documentation I can find for it is the patch submission: http://nhjira.koah.net/browse/NH-1280. – Stuart Childs Mar 12 '09 at 01:31
1

thanks for the info... looks like we'll have to wait for 2.1.0 before this is supported. I believe it's built into Hibernate but NHibernate is still waiting – lomaxx Mar 12 '09 at 01:37
Ah, ok. I end up having to customize NHibernate a lot with stuff like this so I probably just applied that patch to my local build of the library. That would explain why it's not in the documentation though. ;) – Stuart Childs Mar 12 '09 at 01:44
1

Hmm. I just upgraded to 2.1.0 and the ProjectionCriteria method has disappeared. – madcapnmckay Aug 10 '09 at 16:41
I can't find my test case for HAVING... it's possible they just cleaned up the API and you no longer have to explicitly state the projection conditions as such through .ProjectionCriteria. SetProjection returns the criteria object so maybe you're just supposed to add restrictions via ICriteria's .Add. – Stuart Childs Aug 10 '09 at 20:10
1

My bad. It appears that this is only absent on the DetachedCriteria which I was using. – madcapnmckay Aug 14 '09 at 11:26

score 4 · Answer 2 · answered Sep 21 '10 at 13:00

For whomever drops by here with a similar problem, I just solved it this way:

IProjection howMany = Projections.Count("Id").As("HowMany");

ICriteria criteria = session
    .CreateCriteria<L10N>()
    .SetProjection(
        howMany,
        Projections.GroupProperty("Native"),
        Projections.GroupProperty("Locale")
    );

criteria.Add(Restrictions.Gt(howMany,1));

NHibernate Projections and "Having" clause

2 Answers2

Linked