Display table rows with the per-row count of another table using CodeIgniter

I have a view that displays some posts. Each post can have multiple comments and I want to display the total number of comments next to each post.

So far all my database calls are in my controller (I'll be changing this).

function index() {
    $data['query'] = $this->db->get('posts');
    $this->load->view('blog_view', $data);
}

In my view:

<?php foreach($query->result() as $row): 
      <div class="post-box">
          <p><?php echo $row->body; ?><small>&nbsp;added by <?php echo $row->username; ?> on  <?php echo date ('d/m/Y',strtotime($row->created)); ?>&nbsp;<a href="<?php echo base_url(); ?>blog/comments/<?php echo $row->id; ?>"><img src="<?php echo base_url(); ?>images/comments_icon.png" />&nbsp;0</a></small></p>
      </div>
<?php endforeach; ?>

I want to get the total number of comments where comment.post_id = the current record's id. and display it next to the comments icon.

UPDATE

Controller:

function index(){
    //load the model
    $this->load->model('City_model');

    //call the model method
    $data->posts = $this->City_model->get_posts();
    
    
    $this->load->view('blog_view', $data);
}

Model (city_model.php):

<?php

class City_model extends Model{

    function get_posts($id = NULL) {
        
        //define optional id for single post
        //if an id was supplied
        if ( $id != NULL ) {
            $this->db->where('id',$id);
        }

        // execute query
        $query = $this->db->get('posts');

        //make sure results exist
        if($query->num_rows() > 0) {
            $posts = $query->result();
        } else {
            return FALSE;
        }

        //create array for appended (with comments) posts
        $appended_posts_array = array();

        //loop through each post
        foreach ($posts as $post) {

            //get comments associated with the post
            $this->db->where('post_id', $post->id)
            $comments = $this->db->get('comments');

            //if there are comments, add the comments to the post object
            if($comments->num_rows() > 0) {
                $post->comments = $comments;
            }
            else {
                $post->comments = array();
            }

            //rebuild the returned posts with their comments
            $appended_posts_array[] = $post;

        }

        //if post id supplied, only return the single post object
        if ($id != NULL) {
            return $appended_registration_array[0];
        }
        else {
            return $appended_registration_array;
        }
    }
}

Here is an example model method that takes the approach I usually do for Items that have "sub" items...

I generally build them in a Multi-level array in the model like so...

Note: this model returns a post or all posts, complete with an array of associated comments accessable through the ->comments property.

function get_posts($id = NULL) {

    //define optional id for single post
    //if an id was supplied
    if ( $id != NULL ) {
        $this->db->where('id',$id);
    }

    // execute query
    $query = $this->db->get('posts');

    //make sure results exist
    if($query->num_rows() > 0) {
        $posts = $query->result();
    } else {
        return FALSE;
    }

    //create array for appended (with comments) posts
    $appended_posts_array = array();

    //loop through each post
    foreach ($posts as $post) {

        //get comments associated with the post
        $this->db->where('post_id', $post->id)
        $comments = $this->db->get('comments');

        //if there are comments, add the comments to the post object
        if($comments->num_rows() > 0) {
            $post->comments = $comments;
        }
        else {
            $post->comments = array();
        }

        //rebuild the returned posts with their comments
        $appended_posts_array[] = $post;

    }

    //if post id supplied, only return the single post object
    if ($id != NULL) {
        return $appended_registration_array[0];
    }
    else {
        return $appended_registration_array;
    }       
}

Now in the controller...

function posts() {

    //load the model
    $this->load->model('model_name');

    //call the model method
    $data->posts = $this->model_name->get_posts();

    //load the view
    $this->load->view('view/file', $data);

}

Then in the view you can use a nested foreach to loop through the posts AND the comments

<? foreach($posts as $post): ?>                //posts foreach start

    <h1><?= $post->title ?></h1>  //post title
    <p><?= $post->body ?></p>     //post body

    <? foreach($post->comments as $comment): ?>     //comments foreach start       
        <h3><?= $comment->author ?></h3>  //comment author
        <p><?= $comment->body ?></h3>     //comment body
    <? endforeach; ?>                               // end comments foreach

<? endforeach; ?>        // end posts foreach

Take note that once you build the posts array like I have shown in the model, for each $post item you have a $post->comments that is simply an array of comments that are associated with that post, so knowing that, you can call count($post->comments) in the controller or view to get the number of comments related to a single post.

So for your question about just displaying the count, in the view the only change is that you wouldn't loop through all the comments, you'd just do this instead...

<? foreach($posts as $post): ?>                //posts foreach start

    <h1><?= $post->title ?></h1>             //post title
    <p><?= count($post->comments) ?></p>     //comment count


<? endforeach; ?>        // end posts foreach

EDIT : I added the optional parameter $id to the model method so that if you want you can specify a single post if you wish, by passing it's id to the method. That way this same model method can be re-used to display single posts in detail with all comments displayed also.