Help with a query

[eluser]garrettheel[/eluser]
So as part of a test sort of site I'm making, i'm incorporating a blog. So I've got the basics working fine, I just need a little help with the comments side of things. I'm trying to find the best way to display the amount of comments on a given post (only this number, i don't need the actual comments). I tried to do so with a query but ran into some trouble, here it is:

Code:

$this->db->select('blog_posts.id,blog_posts.user_id, blog_posts.title, blog_posts.body, blog_posts.created, blog_posts.modified, users.username AS author');
        $this->db->limit($num);
        $this->db->join('users', 'users.id = blog_posts.user_id', 'left');
        $this->db->join('blog_comments', 'blog_comments.post_id = blog_posts.id', 'left');

From here, I figured I'd use a count in the select part of the query but I've already ran into problems. It seems that the results given from the query are not right - the first "post" result is repeated for each comment that exists. For example, if i have 6 comments in the database, there will be 6 identical results of the first post. Does anyone have a clue what I'm doing wrong? It's obviously something to do with the join query. Thanks in advance.

[eluser]libnac[/eluser]
I think you can do this in a separate model function..
$this->db->where('blog_comments.post_id = $id');
$query = $this->db->get("blog_comments");
$num_rows = $query->num_rows();

Or something like this.. I hope you help this..

[eluser]sophistry[/eluser]
you left out a few important details like your full data model and the rest of that query - where, and get specifically.

in any case, check out these SQL ideas:

DISTINCT (sometimes this helps trim result set dupes)
GROUP_BY and GROUP_CONCAT http://www.mysqlperformanceblog.com/2006/09/04/group_concat-useful-group-by-extension/
SQL_CALC_FOUND_ROWS http://www.mysqlperformanceblog.com/2007...ound_rows/

cheers. let us know how it turns out!

[eluser]garrettheel[/eluser]
Sorry, I didn't think that the rest of the query made much difference, here's the full query

Code:

$this->db->select('blog_posts.id,blog_posts.user_id, blog_posts.title, blog_posts.body, blog_posts.created, blog_posts.modified, users.username AS author');
        $this->db->limit($num);
        $this->db->join('users', 'users.id = blog_posts.user_id', 'left');
        $this->db->join('blog_comments', 'blog_comments.post_id = blog_posts.id', 'left');
        $this->db->order_by('created', 'DESC');
        $query = $this->db->get('blog_posts');

The thing is that it works fine without the second join, where only one of each entry is returned. But there's something wrong with the join that's causing this. Also, I don't really want to put it in a separate model for now because I just want the amount of comments returned with this query. If I put it in another function, I'd have to loop through the results and perform another query for each result getting the amount of comments which is far too many queries then should be needed. Any ideas?

[eluser]sophistry[/eluser]
and the data model?

[eluser]garrettheel[/eluser]
What exactly do you mean by data model? That is one of the functions in my model

[eluser]sophistry[/eluser]
data model is the way your database tables are set up with respect to primary keys and relationships.

as in:
i have three tables:
user
posts
comments

a user can have many posts
a post can have many comments

[eluser]garrettheel[/eluser]
Ah I see, well mine is just how you'd expect..

Three tables (relevant to this question):

blog_posts
blog_comments
users

a user can have many posts
a user can have have many comments
a post can have many comments
posts and comments can only have one user

[eluser]sophistry[/eluser]
why don't you simplify things and just deal with blog_posts and comments and do something like this:

SELECT COUNT(id) AS comment_count FROM blog_comments WHERE blog_comments.blog_post_id = X

obviously change X to whatever post id you are looking at.

EDIT: oops, just looked at the second post in this thread and i've just re-written it. maybe you found that works for you?

[eluser]garrettheel[/eluser]
I can do that, but this is a general query where there is no specific post id to use. It's just getting the 10 latest posts from the database. It's like the query that'd be used on the front page of a wordpress install to let people know the number of comments on each article