0

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?

Tarator
  • 1,517
  • 1
  • 13
  • 30

1 Answers1

0

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));
Vlad Mihalcea
  • 142,745
  • 71
  • 566
  • 911
  • Thanks for your answer, but your suggesetion throws an `NullPointerException`. I had to set an projection, so I tried it this way: (see "Edit A" above) Anyway, this also doesn't give me the result I want, since then I loose the join `p.id = u1.patient_id` in the subquery. The resulting SQL is shown in "Edit B" in your answer. – Tarator Nov 21 '14 at 11:41
  • No, unfortunately this is not what I want... See edit above... The problem is, that I loose the join to the `Patient` of the main query. – Tarator Nov 21 '14 at 11:47
  • But: I fixed this with HQL, which works like a glance. So, thanks for your help. Don't waste more time in this, although I'm still interested in a solution (just to learn things :) – Tarator Nov 21 '14 at 11:49