Hibernate criteria query with subquery joining two columns

Question

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.

Acceptation

Answer 1

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!

Horribly

Answer 2

I have a similar use case. I'm pretty sure it can't be done with Criteria. Hibernate doesn't support joins in the from clause: https://hibernate.atlassian.net/browse/HHH-3356 (still open at time of writing).

The Christmas 2013 answer is pretty good, but unfortunately that correlated subquery is really bad in my use-case of:

• using MySQL
• there can be thousands of dates for each id

But this one is fine (for MySQL 5.6.25 anyway):

SELECT * FROM Quote q1
WHERE (q1.id, q1.date) IN 
(
    SELECT id, max(date)
    FROM Quote
    GROUP BY id, date
)

I came here looking for an answer to

DetachedCriteria maxDateQry = DetachedCriteria.forClass(Quote.class);
maxDateQry.setProjection(
    Projections.projectionList()
        .add(Projections.groupProperty("qid.id"))
        .add(Projections.max("qid.date", "maxdate"))
    );

Criteria criteria = session.createCriteria(Quote.class);
Object environmentIdAndStartedDateProperty = "(environmentId, startedDate)" // but not this, some sort of magic
criteria.add(
    Subqueries.in(environmentIdAndStartedDateProperty, maxDateQry));
List<Quote> quoteList = criteria.list();

But it looks like no such magic exists. I had to give up and use hql, which is probably for the best because according to the Hibernate 4.2 documentation the hibernate criteria API is deprecated anyway: https://docs.jboss.org/hibernate/orm/4.2/devguide/en-US/html/apb.html

Landon

"Such magic" actually exists: criteria.add(Subqueries.propertiesEq(new String[] {"environmentId", "startedDate"}, maxDateQry)); . However it's only implemented after Hibernate 4 :( – Watchful