Which type of join to use for counting?

Question

I tried different join types but couldn't figure out which type should I use. I want to count number of comments for each article.

$select = $this->_db->select()
        ->from($this->_name)
        ->joinLeft('categories', 'categories.cat_id = ' . $this->_name . '.category', array('category_name' => 'name'))
        ->joinLeft('comments', 'comments.post_id = ' . $this->_name. '.post_id', array('num_comments' => 'COUNT(*)'))
        ->group($this->_name.'.post_id')
        ->limit(3)
        ->order("pubDate DESC");
if($category_id > 0) $select->where("category = ?", $category_id);
if($last_pub_date > 0) $select->where("$this->_name.pubDate < ?", $last_pub_date);

I use this method also for my Twitter like pagination, so $last_pub_date is for that. With joinLeft() it seems good but of course if there is no comment for an article query fetches count as 1 instead of 0. I tried with other join types but if there is no comment whole row is not fetched or $this->_db->order("pubDate DESC") is not working correctly. I tried also with subquery but I am not sure if I wrote it as it should be.

Answer 1

The left join is right, but you need to use COUNT(comment_id) instead of COUNT(*). COUNT(*) will return the number of rows, which will be one even if there are no comments. COUNT(comment_id) will return the number of non-NULL comment_id values, which should be what you're looking for.

http://dev.mysql.com/doc/refman/5.1/en/group-by-functions.html#function_count