Hi all,

I've been trying to express this query using the criteria api, without much luck so far. Help will be very appreciated.

Model:

one Patient has many Visits and a Visit belongs to a Patient.

The HQL query:

select p
from Patient p
join p.visits v
group by p
having max(v.startDateTime) >= :date

1st try ( see 16.7. Projections, aggregation and grouping ):

theCriteria = theSession.createCriteria( Visit.class);
theCriteria
.setProjection( Projections.projectionList()
.add( Property.forName("startDateTime").max().as("maxStartDateTime") )
.add( Property.forName("patient").group().as("patient") )
)
.add( Restrictions.ge("maxStartDateTime", dateNumberOfDaysAgo) );

the Criteria.list() method throws an exception starting that the generated sql is not valid.

2nd try: (see 16.8. Detached queries and subqueries )

theCriteria = theSession.createCriteria( Visit.class);

DetachedCriteria maxStartDateTime = DetachedCriteria.forClass(Visit.class, "visit2")
.setProjection( Property.forName("startDateTime").max() )
.add( Property.forName("visit2.patient").eqProperty("visit.patient") );

theCriteria
.add(
Restrictions.conjunction()
.add( Property.forName("startDateTime").eq( maxStartDateTime ) )
.add( Property.forName("startDateTime").lt( dateNumberOfDaysAgo ) )
);

it works, but it is really slow!!!

thanks in advance,
Alexei

Hibernate version:3.0.5
Db: mysql 4.1