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

mysql, php, zend-db, zend-db-select, zend-framework

Solution

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;

Problem

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(...); } ```

Original source