Hello.

I want to realize a statement in HQL or Criteria which works in SQL. The statement returns all mediator(id)s which have more than 250 orders. A order is a combination of the order date (co.created) and the id of a indent or a contract.

I don't know the exactly SQL code now but this pseudo code should explain what i mean.

<code>
select distinct co.mediator_id from COMMISSION co
...
group by co.mediator_id
having count( distinct co.created, isNull(co.indent_id, co.contract_id) ) > 250
</code>

Does anyone know how to realize this?

Best regards

I think you can approach this in 2 steps:

1. on your pojo define the order as a property and for the mapping use a formula, the formula will implement in sql your predicate
http://www.hibernate.org/hib_docs/annotations/api/org/hibernate/annotations/Formula.html

2. in HQL/Criteria use the property name that you defined above

This approach has the added benefit of explicitly exposing the concept of 'hasAnOrder' and this may prove useful in other parts of the design AND/OR ease maintenance moving forward.

Marius

I'm not so familiar with Hibernate so let see if I got this right.

Now I have 3 Java classes (Commission, Ident, Contract). Each of them is mapped with Hibernate to a DB table.

I create a new Java class (Order) with 3 attributes (mediator_id,created, order_id) which is not mapped to a DB table but mapped with a formular in native SQL.
Right?

For my example the SQL should look like this?

<code>
select distinct co.mediator_id, co.created, isNull(co.indent_id, co.contract_id) from COMMISSION co
</code>

Now the Order can be used in the having/count part, correct?

It's working now. Thanks Marius.

Just so that there's a record when the next person tries to ask this, yes: "having count(distinct user.foo)" works, but "having count(distinct lower(user.foo))" does not.