title: Grails "max" subquery with an association, to get only the latest of a hasMany
body: <p>The simplified domain model:
  'Txn' (as in Transaction) hasMany 'TxnStatus'.  TxnStatus has a dateTime </p>
  
  <p>This is a legacy mapping so I cant change the DB, the mapping on Txn:</p>
  
  <pre><code>  static mapping = { 
      txnStatus column: 'MessageID', ignoreNotFound: true, fetch: 'join'
    }
  </code></pre>
  
  <p>I need to get Txns based on a number of dynamically built criteria, currently using GORM's 'where' query, it works well; BUT I need to also get only the <strong>latest</strong> txnStatus.</p>
  
  <p>Tried:</p>
  
  <pre><code>def query = Txn.where {
     txnStatus { dateTime == max(dateTime) }    
  }
  </code></pre>
  
  <p>gives:  <code>java.lang.ClassCastException: org.hibernate.criterion.DetachedCriteria cannot be cast to java.util.Date</code></p>
  
  <p>also tried:</p>
  
  <pre><code> def query = Txn.where {
         txnStatus.dateTime == max(txnStatus.dateTime)    
      }
  </code></pre>
  
  <p>which gives:</p>
  
  <pre><code>Compilation Error: ... 
              Cannot use aggregate function max on expressions "txnStatus.dateTime"
  </code></pre>
  
  <p>At this stage I am thinking of changing to HQL...any help appreciated!</p>