I have a table, "Quote", mapped in hibernate that has a composite key of an integer id and a date, and several additional columns. I'd like to write a criteria query that uses a DetachedCriteria to get the row for each id with the greatest date.
In sql, I might write a query like
SELECT * FROM Quote q1
INNER JOIN (SELECT id, max(date) as maxdate FROM Quote
GROUP BY id, date) q2
ON q1.id = q2.id AND q1.date = q2.maxdate
In hibernate, I think can create a DetachedCriteria for the "group by" subquery like this (where Quote is the class mapping the table, and "Qid" is a composite id class for the key, with properties id and date, accessed by a "qid" property of the Quote class):
DetachedCriteria maxDateQry = DetachedCriteria.forClass(Quote.class);
maxDateQry.setProjection(
Projections.projectionList()
.add(Projections.max("qid.date", "maxdate"))
.add(Projections.groupProperty("qid.id")));
However, I'm not sure how to use this in a criteria query that would be equivalent to the outer part of the sql, above. I'm looking for something along the lines of
Criteria criteria = session.createCriteria(Quote.class);
criteria.add(
Restrictions.and(
Property.forName("qid.id").eq(maxDateQry???),
Property.forName("qid.date").eq(maxDateQry???)));
List<Quote> quoteList = criteria.list();
Where the two Property.forName's above relate the outer table to the corresponding columns of the subquery. If the inner join provided only one value, I would simply give the DetachedCriteria a single Projection and pass the DetachedCriteria straight into Property.forName(...).eq(..). I'm not sure how to use the DetachedCriteria with two values (id and maxdate) in the Projection.
Had exactly the same problem and couldn't find an exact solution for a multi-column subquery match. What I did was rewrite the query so that it only needs to match the subselect on ONE column. So instead of
SELECT *
FROM Quote q1
INNER JOIN (
SELECT id, MAX(date) AS maxdate
FROM Quote
GROUP BY id
) q2
ON q1.id = q2.id AND q1.date = q2.maxdate;
Try:
SELECT *
FROM Quote q1
WHERE q1.date = (SELECT MAX(date) FROM Quote inner where inner.id = q1.id)
The key difference is that all bar the MAX condition of the JOIN criteria is now inside the inner SELECT.
The criteria/detached criteria for this looks like:
DetachedCriteria innerCriteria = DetachedCriteria.forClass(Quote.class, "inner")
.add(Restrictions.eqProperty("inner.id","q1.id"))
.setProjection(Projections.projectionList().add(Projections.max("inner.date")));
DetachedCriteria outerCriteria= DetachedCriteria.forClass(ClmClaim.class, "q1");
outerCriteria.add(Subqueries.propertyEq("q1.date", innerCriteria ));
The SQL produced looks like:
select
this_.<Blah> as blah2_49_0_,
this_.<Blah> as blah2_50_0_,
this_.<Blah> as blah2_51_0_,
from
Quote this_
where
this_.date = (
select
max(inner_.date) as y0_
from
Quote inner_
where
inner_.claim_number=this_.claim_number
);
You'll note that there is no 'group by' in the SQL because it's not needed. I would expect one to be there if there were more than one match condition in the subselect.
Anyway, hope it helps. It's the last thing I'm going to do before Christmas!