title: Kohana ORM, rewriting an ORDER BY and GROUP BY query
body: <p>I need to get a list of Articles sorted by the latest Comment from a related table joined on article.id = message.article_id using Kohana ORM. I managed to build a query that GROUPS and only then ORDERS:</p>
  
  <pre><code>SELECT * 
  FROM  `articles` 
  LEFT JOIN  `comments` ON (  `articles`.`id` =  `comments`.`article_id` ) 
  GROUP BY  `comments`.`item_id` 
  ORDER BY  `datetime` DESC 
  </code></pre>
  
  <p>The query I am trying to build is:  </p>
  
  <pre><code>SELECT * FROM `articles` LEFT JOIN 
  (SELECT article_id, MAX(datetime) as datetime FROM comments GROUP BY (article_id)) 
  AS b ON `articles`.`id` = b.`article_id` 
  ORDER BY datetime 
  </code></pre>
  
  <p>I have no idea how to rewrite it into Kohana ORM... (and I can't avoid ORM because there is a ton of code that depends on it)</p>