Conditional limit in mySQL query possible?

Question

i am faced with a decicion regarding handling threaded comments in our project... I have a simple MySQL table which holds all the comments. There are two types: parents and childs. Childs represent a reply to a parent or another child.

My problem:

-Comment (depth 0)
-- Reply Child (depth 1)
--- Reply to previous child (depth 2)
-Comment (depth 0)

Imagine the above structure and a MySQL query with LIMIT 2. It would cut of the last reply (depth 2). Actually i would like to say something like: Try to limit to 2, if child left go on until the next parent. Tried several queries with no luck...

What i have right now is as followed:
SELECT SQL_CALC_FOUND_ROWS * FROM comments WHERE comment_post_id = '{$_REQUEST["ID"]}' ORDER BY comment_id, comment_date DESC LIMIT 10"

The important table fields are:
comment_id (index) | comment_parent_id (contains comment_id of parent or NULL)| comment_date

I would be very thankful for any ideas!!!

Saludos, Booosh

Answer 1

I was faced with this same problem, only I was going only one depth deep.

--Comment (depth: 0)
---Reply  (depth: 1)

I managed to use a single query to select all these records, while limiting the top level Comment records to only 10.

SELECT c.* FROM comments AS c WHERE c.OwnerId = 1 AND c.ParentId = 0 LIMIT 10
UNION
SELECT cc.* FROM comments AS cc
    INNER JOIN
    (
        SELECT CommentId FROM comments WHERE OwnerId = 1 AND ParentId = 0 LIMIT 10
    )
    AS c2
    ON cc.ParentId = c2.CommentId

This query basically does the following:

• Gets the first 10 records that are top level comments, and have a specific owner id.
• Gets all comments that have a parent id equal to the comment id that was returned by the first query, and unions them to the result set.

While, I think this query would be more efficient than making multiple calls to the database for each record, it still does have the flaw that it executes the first query twice. Once before the union, and one in the join on the union.

It does seem to be fairly quick, but not as quick as I would like. However, if your database is remote, and latency is an issue, this solution might serve you better than making multiple remote queries to the database.