title: Hibernate criteria query using Max() projection on key field and group by foreign primary key
body: <p>I'm having difficulty representing this query (which works on the database directly) as a criteria query in Hibernate (version 3.2.5):</p>
  
  <pre><code>SELECT s.* 
    FROM ftp_status s 
   WHERE (s.datetime,s.connectionid) IN (SELECT MAX(f.datetime),
                                                f.connectionid 
                                           FROM ftp_status f
                                          GROUP BY f.connectionid);
  </code></pre>
  
  <p>so far this is what I've come up with that doesn't work, and throws a <code>could not resolve property: datetime of: common.entity.FtpStatus</code> error message:</p>
  
  <pre><code>Criteria crit = s.createCriteria(FtpStatus.class);
  crit = crit.createAlias("connections", "c");
  crit = crit.createAlias("id", "f");
  ProjectionList proj = Projections.projectionList();
  proj = proj.add(Projections.max("f.datetime"));
  proj = proj.add(Projections.groupProperty("c.connectionid"));
  crit = crit.setProjection(proj);
  List&lt;FtpStatus&gt; dtlList = crit.list();
  </code></pre>
  
  <p>Here's the relevant reference configuration that Netbeans 6.8 generated directly from the database:</p>
  
  <p>FtpStatus.hbm.xml - </p>
  
  <pre><code>&lt;hibernate-mapping&gt;
     &lt;class name="common.entity.FtpStatus" table="ftp_status" catalog="common"&gt;
          &lt;composite-id name="id" class="common.entity.FtpStatusId"&gt;
              &lt;key-property name="siteid" type="int"&gt;
                  &lt;column name="siteid" /&gt;
              &lt;/key-property&gt;
              &lt;key-property name="connectionid" type="int"&gt;
                  &lt;column name="connectionid" /&gt;
              &lt;/key-property&gt;
              &lt;key-property name="datetime" type="timestamp"&gt;
                  &lt;column name="datetime" length="19" /&gt;
              &lt;/key-property&gt;
          &lt;/composite-id&gt;
          &lt;many-to-one name="connections" class="common.entity.Connections" update="false" insert="false" fetch="select"&gt;
              &lt;column name="connectionid" not-null="true" /&gt;
          &lt;/many-to-one&gt;
          &lt;many-to-one name="sites" class="common.entity.Sites" update="false" insert="false" fetch="select"&gt;
              &lt;column name="siteid" not-null="true" /&gt;
          &lt;/many-to-one&gt;
          &lt;property name="upInd" type="boolean"&gt;
              &lt;column name="up_ind" not-null="true" /&gt;
          &lt;/property&gt;
          &lt;property name="lastMessage" type="string"&gt;
              &lt;column name="last_message" length="65535" not-null="true" /&gt;
          &lt;/property&gt;
      &lt;/class&gt;
  &lt;/hibernate-mapping&gt;
  </code></pre>
  
  <p>Connections.hbm.xml - </p>
  
  <pre><code>&lt;hibernate-mapping&gt;
      &lt;class name="common.entity.Connections" table="connections" catalog="common"&gt;
          &lt;id name="connectionid" type="java.lang.Integer"&gt;
              &lt;column name="connectionid" /&gt;
              &lt;generator class="identity" /&gt;
          &lt;/id&gt;
          &lt;property name="ip" type="string"&gt;
              &lt;column name="ip" length="15" not-null="true" /&gt;
          &lt;/property&gt;
          &lt;property name="port" type="int"&gt;
              &lt;column name="port" not-null="true" /&gt;
          &lt;/property&gt;
          &lt;property name="user" type="string"&gt;
              &lt;column name="user" length="8" not-null="true" /&gt;
          &lt;/property&gt;
          &lt;property name="password" type="string"&gt;
              &lt;column name="password" length="50" not-null="true" /&gt;
          &lt;/property&gt;
          &lt;set name="ftpStatuses" inverse="true"&gt;
              &lt;key&gt;
                  &lt;column name="connectionid" not-null="true" /&gt;
              &lt;/key&gt;
              &lt;one-to-many class="common.entity.FtpStatus" /&gt;
          &lt;/set&gt;
      &lt;/class&gt;
  &lt;/hibernate-mapping&gt;
  </code></pre>
  
  <p>I know I'm missing something, but my googling on hibernate hasn't revealed it yet.  Alternatively a SQL query directly using <code>s.createSQLQuery()</code> or <code>s.createQuery()</code> is also acceptable, but I've had even less success writing that one..... </p>