Showing number of comments in blog (while listing all posts)

[eluser]jedd[/eluser]
I'm a big advocate of writing dodgy SQL and coming back to it later to fix it up .. but that's because I'm on the steep bit of the learning curve with SQL, and keep finding new and neat ways of doing things.

So, from the code you've given in your first message, and with the caveat that I always use arrays rather than objects (and consequently this code is UNTESTED) I'd suggest you could start with something like this. You're more familiar with your own data structures, so it should be easy to make this much better. And yes, I really should get my head around object fiddling one day.

Code:

function get_all_posts() {
    $q = $this->db->get('posts');
    $results = q->result_array();
    $retval = array();
    $x = 0;
    foreach ($results as $result)  {
         $retval[$x] = $result;
         $retval[$x]['comment_count'] = $this->count_all_comments($result['post_id']);
         $x++;
         }
    return $retval;  // Note this is an array, not an object, of course.
    }

function count_all_comments($post_id) {
    $this->db->where('post_id', $post_id);
    $q = $this->db->count_results('comments');
    return $q->result();
    }

Mind, as has been pointed out already, you really should be doing all these grabs in a single database query.