title: Hibernate criteria query with subquery joining two columns
body: <p>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.</p>
  
  <p>In sql, I might write a query like</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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):</p>
  
  <pre><code>DetachedCriteria maxDateQry = DetachedCriteria.forClass(Quote.class);
  maxDateQry.setProjection(
      Projections.projectionList()
          .add(Projections.max("qid.date", "maxdate"))
          .add(Projections.groupProperty("qid.id")));
  </code></pre>
  
  <p>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</p>
  
  <pre><code>Criteria criteria = session.createCriteria(Quote.class);
  criteria.add(
      Restrictions.and(
          Property.forName("qid.id").eq(maxDateQry???),
          Property.forName("qid.date").eq(maxDateQry???)));
  List&lt;Quote&gt; quoteList = criteria.list();
  </code></pre>
  
  <p>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.</p>