My table ofcomments looks like:
| ID | article_id | user_id | ...
|-------------------------------|
| 1 | 1 | 1 | ...
| 2 | 2 | 2 | ...
| 3 | 2 | 1 | ...
| 4 | 3 | 2 | ...
AndI need to get top 5 articles with the most comments. When I use this statement in SQL console SELECT 'article_id', count(*) as 'total' FROM 'comments' GROUP BY 'article_id' ORDER BY 'total' LIMIT 5
, then I get everything I want. But I need to do this with NotORM and this is where I stucked at. This is my function to get these articles:
function getBestActive() {
$items = $this->db->comments()
->select("article_id, count(*) as 'total'")
->order("total DESC")
->limit(5);
$articles = array();
foreach($items as $item) {
$article = $this->db->article('id', $item['article_id'])->fetch();
$article['img'] = "thumb/{$article['uri']}.jpg";
$article['comments'] = $item['total'];
$articles[] = $article;
}
return $articles;
}
But it returns me array with only 1 article (the most commented) and I need the most 5 articles. Or is it possible to execute custom SQL statement with NotORM (that could be answer too)?
Oh now I see. I forget to add group()
function. So using this selection everything works:
$items = $this->db->comments()
->select("article_id, count(*) as 'total'")
->group("article_id")
->order("total DESC")
->limit(5);