Get total comments of article in PHP with NotORM

Question

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)?

Answer 1

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);