[solved] Comment Counting Again

[eluser]Unknown[/eluser]
Firstly I'm sorry to bring this up again. I'm aware this question has been asked a couple of times before but none of the solutions I've read seem to work for me.

Basically what I'm trying to do is add the number of comments to the "comments" link in a Blog. So it it'd be like:

Code:

Title: Post
Body: Blah Blah
Comments (10)

It's really frustrating because it seems like such a simple problem if not for the separation of logic from the view file.

Right now I have a model that contains this:

Code:

function getEntries()
{
    $query['entries'] = $this->db->get('entries');    
    return $query;
}

getEntries is called in my Blog controller here:

Code:

function blogPosts()
{
    $this->load->model('Blog_model');
    $data['entries'] = $this->Blog_model->getEntries();
    
    $this->load->view('blog_view', $data);
    
}

...and finally this is sent off to be displayed in the view here:

Code:

<?php foreach($entries->result() as $row):?>
    <?php echo $row->title;?>
    <?php echo $row->date;?>
    <?php echo $row->body;?>
    <?php echo anchor('blog/comments/'.$row->ID, ' Comments');?>
<?php endforeach; ?>

Back when I used to code things in regular PHP I'd of just done a count SQL query for each iteration of the loop (using the row ID to decide which comments are relevant to each individual blog post) but with this new-fangled MVC business I need to already have everything sorted out before I get anywhere near the loop.

I think what I need to do is essentially create a new temporary table containing only entry IDs and a count for the number of comments associated with this entry id. Then if I could join this to the entries table (on the entry id) it would be easy to output the relevant data at the view stage... on the other hand I might be completely wrong.

If anyone could help me I would be extremely grateful.

Thanks.

ADDED: One other thing - here are my table structures:

Entries:
ID
Title
Date
Body

Comments:
ID
entry_ID
Body
Author

[eluser]darkhouse[/eluser]
change your get_entries sql to this:

Code:

function getEntries()
{
    $this->db->select('e.*, COUNT(c.entry_ID) AS total_comments');
    $this->db->join('comments c', 'c.entry_ID = e.id');
    $this->db->group_by('c.entry_ID');
    $query['entries'] = $this->db->get('entries e');    
    return $query;
}

you should now have a new property in your row object called total_comments which should be the count of all comments for each blog entry.

[eluser]Unknown[/eluser]
Thank you. This looks to be exactly what I need. However there seems to be a slight problem.

Code:

Unknown column 'COUNT(e.entry_id)' in 'field list'

SELECT `c`.*, `COUNT(e`.`entry_id)` AS total_comments FROM (`entries` e) JOIN `comments` c ON `c`.`entry_id` = `e`.`id` GROUP BY `c`.`entry_id`

As you could probably guess I'm slightly bad at SQL but I can't see anything wrong with this. The only thing that looks weird to me is how it's trying to select entry_id from the e(entries) table (being that this only exists in comments). Though I guess SELECT happens last so it wouldn't matter...

Anyone have any thoughts on this?

Thanks again.

ADDED: I've solved this problem. What I ended up doing was just writing a regular SQL statement to do the same thing that darhouse posted. I guess his version would've worked save for some minor detail that I could not identify due to my lack of experience with ActiveRecord.

Here is my final code:

Code:

function getEntries()
{
    $query = $this->db->query('SELECT entries.*, COUNT(comments.entry_id) AS total_comments FROM entries LEFT JOIN comments ON comments.entry_id = entries.id GROUP BY entries.id ORDER by entries.id');
        return $query;
}

Thanks again for the help though darkhouse. You put me on the right track and I guess the fact that I wasn't able to copy and paste the fix made it a much more effective learning experience.

[eluser]darkhouse[/eluser]
Ah, I think all that was required was to add FALSE into the select method, like this:

Code:

$this->db->select('e.*, COUNT(c.entry_ID) AS total_comments', FALSE);

Active Record was putting backquotes in where it shouldn't have, setting that 2nd parameter to FALSE makes it not do that. I should've known, my bad.