Datamapper Count() Issue

[eluser]Unknown[/eluser]
I am having trouble counting how many comments an article has. It is one to many relationship and I have a column in the comments table called (article_id) and on my index page where are displayed all the articles I want to get how many comments each one has.

I am using DataMapper. What this code gives me if I have 2 Articles the oldest one has 7 comments and the new one has 2 comments then it will display the noOfComments of the new one on both articles. So Instead of being Comments:2 Comments:7 is Comments: 2 and again Comments: 2. How to make it work properly ? I read the manual and a tried several things but without a luck Any help ?

Code:

$articles = new Article_model();
$comments = new Comment_model();

$articles->order_by('pubdate', 'desc')->get();
$id = $articles->id;

$noOfComments = $comments->where('article_id', $id)->count();

$recent_articles = array();

foreach ($articles as $article)
{
  $single_article = array(
            'id' => $article->id,
            'title' => $article->title,
            'pubdate' => $article->pubdate,
            'text' => $content,
            'cover_img' => $article->cover_img,
            'noOfComments' => $noOfComments,
            );
   array_push($recent_articles, $single_article);
}

[eluser]GrahamDj28[/eluser]
The reason you are getting the same result for each article is because you are only executing the query once.
Remove these two lines.

Code:

//$id = $articles->id;
//$noOfComments = $comments->where('article_id', $id)->count();

You must get the number of comments in your foreach.
Not outside of it.

Code:

$recent_articles = array();

/* Changes your foreach loop to this */
foreach ($articles as $article)
{
  $single_article = array(
            'id' => $article->id,
            'title' => $article->title,
            'pubdate' => $article->pubdate,
            'text' => $content,
            'cover_img' => $article->cover_img,
            /* Get the number of comments here */
            'noOfComments' => $comments->where('article_id', $article->id)->count();
            );
   array_push($recent_articles, $single_article);
}

[eluser]Unknown[/eluser]
yeah I fixed, I forgot to comment here. Thanks anyway