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?

Answer 1

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)))