CODEWITHSUNDEEP
mysqlormkohana
user1158010
user1158010

Reputation: 53

Kohana ORM, rewriting an ORDER BY and GROUP BY query

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:

SELECT * 
FROM  `articles` 
LEFT JOIN  `comments` ON (  `articles`.`id` =  `comments`.`article_id` ) 
GROUP BY  `comments`.`item_id` 
ORDER BY  `datetime` DESC

The query I am trying to build is: 

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

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)

Upvotes: 1

Views: 5004

Answers (2)

Kemo
Kemo

Reputation: 7042

$subquery = DB::select('article_id', array('MAX("datetime")','datetime'))
    ->from('comments')
    ->group_by('article_id');

$s = ORM::factory('article')
    ->join(array($subquery, 'b'), 'LEFT')
    ->on('article.id','=','b.article_id')
    ->order_by('datetime')
    ->find_all();

This is the translation of your query, I'm not really sure if it will work

Upvotes: 3

Rojan
Rojan

Reputation: 255

ORM::factory('article')->join('comments', 'LEFT')->on('article.id', '=', 'comments.article_id')->group_by('comments.id')->order_by('date', 'DESC')->find_all()->as_array();

This generates sql as :

SELECT article.* FROM articles AS article LEFT JOIN comments ON (article.id = comments.article_id) GROUP BY comments.id ORDER BY date DESC

which matches your first query.

I'm not sure how to use nested query in ORM but there is Query builder in kohana which should do the trick for you.

Upvotes: 1

Related Questions