Hibernate DetachedCriteria Query with and clause in subselect

Question

How can I solve this query with Hibernate's detached criteria? The hardest part for me is to bring the and u1.abrechnungsDatum is null into the subselect. I want a query like this:

select * 
from 
   patient as p
where 
   p.krankenstand = ?
and 
   ? < ( select count(*) from ueberweisung u1 where  p.id = u1.patient_id
         and u1.abrechnungsDatum is null)

I have tried it with this

DetachedCriteria dc = DetachedCriteria.forClass(Patient.class, "patient");
dc.add(Restrictions.eq("krankenstand", true));

DetachedCriteria nab = dc.createCriteria("ueberweisungs", "nab");
nab.add(Restrictions.isNull("nab.abrechnungsDatum"));
dc.add(Restrictions.sizeGt("ueberweisungs", 0));

which gives me the following SQL-Statement

select
    *
from
    patient this_ 
inner join
    ueberweisung nab1_ 
        on this_.id=nab1_.patient_id 
where
    this_.krankenstand=? 
    and nab1_.abrechnungsDatum is null 
    and ? < (
        select
            count(*) 
        from
            ueberweisung 
        where
            this_.id=patient_id
    )

As you can see, the and-clause for the field abrechnungsDatum is not inside the subselect. How can I achieve to get this inside?

Answer 1

Try this instead:

DetachedCriteria dc = DetachedCriteria.forClass(Patient.class, "patient");
dc.add(Restrictions.eq("krankenstand", true));

DetachedCriteria subquery = DetachedCriteria.forClass(Ueberweisung.class);
subquery.add(Restrictions.isNull("abrechnungsDatum"));

dc.add(Subqueries.ltAll(0, subquery));