MySQL Query Count Problem

Question

I'm trying to count my comments and there replies but I can't seem to get it right.

Here is my query so far.

SELECT posts_comments.*, users.* 
(SELECT COUNT(*)
FROM posts_comments 
WHERE parent_comment_id >= 1)
FROM posts_comments
LEFT JOIN users 
ON posts_comments.user_id = users.user_id
WHERE post_id = '" . $post_id . "'
AND parent_comment_id = 0
LIMIT $start, $display

And here is my MySQL table.

CREATE TABLE posts_comments (
comment_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
parent_comment_id INT UNSIGNED NOT NULL,
user_id INT UNSIGNED NOT NULL,
post_id INT UNSIGNED NOT NULL,
comment TEXT NOT NULL,
PRIMARY KEY (id),
);

Display Output.

COMMENT 1
   comment 2
   comment 3
COMMENT 4
COMMENT 5

Answer 1

You are organizing your hierarchical data using the adjacency list model. The fact that such recursive operations tend to be difficult is in fact one major drawback of this model.

Some DBMSes, such as SQL Server 2005, Postgres 8.4 and Oracle 11g R2, support recursive queries using common table expressions with the WITH keyword (also see @OMG Ponies' comment below). This feature allows queries such as this to be implemented easily, but unfortunately MySQL does not support recursive queries yet.

In the following series of articles, @Quassnoi suggests one solution to tackle hierarchical queries in MySQL, which you may want to check out:

• Quassnoi: Hierarchical queries in MySQL

You may also may be interested in checking out the following article which describes an alternative model (the nested set model), which makes recursive operations easier:

• Mike Hillyer: Managing Hierarchical Data in MySQL

In addition, I also suggest checking out the following presentation by @Bill Karwin:

• Bill Karwin: Models for hierarchical data with SQL and PHP

The closure table model described in the presentation is a very valid alternative to the nested set. He further describes this model in his SQL Antipatterns book (excerpt from the chapter on this topic [PDF]).

Otherwise, you could also consider downloading all the data to your application, building a tree, and walking through it.

Answer 2

You're not gonna be able to have the count of the parent comments and the children comments on the same query. You will have to separate and send a query every time you hit a child if you want the output like you posted. (use a for()) Or have another column and have your program break down the results.

Also, in your query, you have AND parent_comment_id = 0 while the other SELECT has AND parent_comment_id >= 1 That's always going to bring you zero results.