Multiple conditions in HAVING clause with NHibernate Criteria?

Question

I'm trying to use NHibernate's Criteria API to write the equivalent of this:

select foo_id from foo_history
group by foo_id
having sum(bar_in) > 0 or sum(baz_in) > 0;

with this mapping:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="MVC"
                   namespace="MVC.Model.Things">
  <class name="MVC.Model.Things.FooHistory, MVC"
         table="foo_history">
    <id name="ID" column="foo_hist_id" type="guid"
        unsaved-value="00000000-0000-0000-0000-000000000000">
      <generator class="guid.comb" />
    </id>

    <!-- Properties -->
    <property name="BarIn" column="bar_in" type="decimal"
              precision="19" scale="4" not-null="true" />
    <property name="BazIn" column="baz_in" type="decimal"
              precision="19" scale="4" not-null="false" />

    <!-- Foreign Keys -->
    <many-to-one name="Foo" column="foo_id"
                 class="MVC.Model.Things.Foo, MVC.Model.Things"
                 not-null="true" />
  </class>
</hibernate-mapping>

and this Criteria code (Detached because it will be a subquery):

var results = DetachedCriteria.For<FooHistory>("fh")
    .SetProjection(Projections.ProjectionList()
        .Add(Projections.GroupProperty(Projections.Id()))
        .Add(Projections.Sum("fh.BarIn"))
        .Add(Projections.Sum("fh.BazIn")))
    .Add(Restrictions.Gt(Projections.Sum("fh.BarIn"), 0) ||
        Restrictions.Gt(Projections.Sum("fh.BazIn"), 0))
    .GetExecutableCriteria(session).List();

The problem is that adding a conditional restriction after the SetProjection() results in NHibernate generating invalid MySQL:

SELECT this_.foo_hist_id as y0_,
       sum(this_.bar_in) as y1_,
       sum(this_.baz_in) as y2_
FROM foo_history this_
WHERE (sum(this_.bar_in) > ?p0
       or sum(this_.baz_in) > ?p1)
GROUP BY this_.foo_hist_id

...using a WHERE instead of a HAVING. Using a single restriction works fine and everything is correct. I assume that since HN-1280 ("Adds HAVING support to CreateCriteria queries, Fixes parameter order bugs") this is possible but I'm not using the correct "OR" language (e.g., Restrictions.Disjunction() just always creates WHERE).

Is this possible?

Have you tried: Restrictions.Or(Restrictions.Gt(Projections.Sum("fh.BarIn"), 0), Restrictions.Gt(Projections.Sum("fh.BazIn"), 0)) — bonifaz, Nov 14 '12 at 14:45
@bonifaz: The OR operator is overloaded to provide an `OrExpression`: https://github.com/nhibernate/nhibernate-core/blob/master/src/NHibernate/Criterion/AbstractCriterion.cs#L56-59 ; so it is equivalent. — wes, Nov 14 '12 at 15:04

score 2 · Answer 1 · answered Dec 22 '12 at 19:40

I faced with this problem, however i don't think my workaround was good cause it doesn't work for example when you need ToRowCountQuery() for pagination. But anyway... You can use this class instead of built-in OrExpression

class OrHaving : OrExpression
    {
        public OrHaving(ICriterion lhs, ICriterion rhs) : base(lhs, rhs)
        {
        }

        public override IProjection[] GetProjections()
        {
            return LeftHandSide.GetProjections().Concat(RightHandSide.GetProjections()).ToArray();
        }
    }

like

.Add(new OrHaving (Restrictions.Gt(Projections.Sum("fh.BarIn"), 0) ,
                   Restrictions.Gt(Projections.Sum("fh.BazIn"), 0)))

Thanks sir, I am very grateful for your answer. From all the different answers and approached I tried, this is the only one that worked for me. (I also inherited the AndExpression, it works like a charm) — kerzek, Jul 21 '15 at 19:30

Multiple conditions in HAVING clause with NHibernate Criteria?

1 Answers1