MySQL Query - Select all posts and count comments for each one

Question

It's 3:30 AM in my country so I need to sleep but I can't without this:

I'm trying to get all posts (using Zend_Db) and count comments for each one.

Schema

blog_posts:

+---------------+------------------+------+-----+---------+----------------+
| Field         | Type             | Null | Key | Default | Extra          |
+---------------+------------------+------+-----+---------+----------------+
| id            | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| title         | varchar(255)     | NO   |     | NULL    |                |
| content       | text             | NO   |     | NULL    |                |
| alias         | varchar(100)     | NO   |     | NULL    |                |
| user_id       | int(11)          | NO   |     | NULL    |                |
| created_date  | datetime         | NO   |     | NULL    |                |
| modified_date | datetime         | YES  |     | NULL    |                |
| thumbnail     | varchar(255)     | YES  |     | NULL    |                |
+---------------+------------------+------+-----+---------+----------------+

And here's blog_comments:

+---------------+------------------+------+-----+---------+----------------+
| Field         | Type             | Null | Key | Default | Extra          |
+---------------+------------------+------+-----+---------+----------------+
| id            | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| user_id       | int(11)          | NO   |     | NULL    |                |
| post_id       | int(11)          | NO   |     | NULL    |                |
| comment       | text             | NO   |     | NULL    |                |
| created_date  | datetime         | NO   |     | NULL    |                |
| modified_date | datetime         | YES  |     | NULL    |                |
+---------------+------------------+------+-----+---------+----------------+

Note: the blog_comments.post_id is linked with blog_posts.id.

I would like a resulting table like that:

+---------------+------------------+------+-----+---------+----------------+
| Field         | Type             | Null | Key | Default | Extra          |
+---------------+------------------+------+-----+---------+----------------+
| id            | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| title         | varchar(255)     | NO   |     | NULL    |                |
| content       | text             | NO   |     | NULL    |                |
| alias         | varchar(100)     | NO   |     | NULL    |                |
| user_id       | int(11)          | NO   |     | NULL    |                |
| created_date  | datetime         | NO   |     | NULL    |                |
| modified_date | datetime         | YES  |     | NULL    |                |
| thumbnail     | varchar(255)     | YES  |     | NULL    |                |
+---------------+------------------+------+-----+---------+----------------+
| TEMPOROARY COLUMN IN OBJECT ($post->comment)                             |
+---------------+------------------+------+-----+---------+----------------+
| comments      |                  |      |     |         |                |
+---------------+------------------+------+-----+---------+----------------+

Now, here's the query I have for now:

SELECT `p`.*, `c`.*
FROM `blog_posts` `p`
LEFT JOIN (
    SELECT COUNT(*) 
    FROM `blog_comments` `c`
    WHERE c.post_id = p.id
) ON `p`.`comments`;

But it give me an error:

Error Code: 1248. Every derived table must have its own alias

So if someone can help me, it would be very appreciated!

IMPORTANT NOTE I'm using Zend_Db and Zend_Db_Select so I must be able to use the functions like joinLeft() or anything I need.

This is in my model for the select():

$select = $this->table->select();

if ($alias) {
    $select->where('alias = ?', $alias);
    return $this->table->fetchRow($select);
}
if ($withComments) {
    // I WILL PLACE THE CODE HERE, EXEMPLE:
    $select->joinLeft(...);
}

To get rid of the error message, you will have to write JOIN (SELECT ...) _AS counts_ ON ... However, there seems to be other problems with your query as well (you should e.g return a key together with the count from the subselect, and the on clause is missing a condition. — Terje D., Oct 31 '12 at 08:06
@TerjeD. Can you explain a little please? I know MySQL for the base query but when I need to do something harder, I refer to SO 'cause the `JOIN` and etc. aren't my force! — Frederick Marcoux, Oct 31 '12 at 08:10
Show an example of what the resulting table should look like. — Terje D., Oct 31 '12 at 08:21

score 4 · Accepted Answer · answered Oct 31 '12 at 08:35

4

SELECT p.*, x.*
FROM blog_posts p
LEFT JOIN 
(
    SELECT post_id, COUNT(*) as cc
    FROM blog_comments
    GROUP BY post_id
) x 
ON x.post_id = p.id;

answered Oct 31 '12 at 08:35

davek

22,499
9
75
95

`Error Code: 1054. Unknown column 'p.id' in 'where clause'` – Frederick Marcoux Oct 31 '12 at 08:36
@Frederick: I've edited it to remove the WHERE in the inline select – davek Oct 31 '12 at 08:37
Now, I need to convert it to a `Zend_Db_Select` -_- – Frederick Marcoux Oct 31 '12 at 08:39

MySQL Query - Select all posts and count comments for each one

1 Answers1