Dependent MySQL SELECT

My problem is the following:

My tables are MESSAGE and MESSAGE_COMMENT,

MESSA开发者_如何转开发GE (id,content)

MESSAGE_COMMENT (id, message_id, content)

I need to select all messages and max 3 comments for each message, like in this example:

type | id | content
M      15   "this is a message with no comments"
M      16   "this is another message with many comments"
R      16   "comment1"
R      16   "comment2"
R      16   "comment3"
M      17   "this is another message with no comments"

"id" is MESSAGE.id when it's a message and COMMENT.message_id when it's a comment.

I hope I have clearly explained my problem..

---

SELECT  *
FROM    (
        SELECT  m.id,
                COALESCE(
                (
                SELECT  id
                FROM    message_comment mc
                WHERE   mc.message_id = m.id
                ORDER BY
                        mc.message_id DESC, id DESC
                LIMIT 2, 1
                ), 0) AS mid
        FROM    message m
        ) mo
LEFT JOIN
        message_comment mcd
ON      mcd.message_id >= mo.id
        AND mcd.message_id <= mo.id
        AND mcd.id >= mid

Create an index on message_comment (message_id, id) for this to work fast.

See this article in my blog for more detailed explanation of how this works:

• Advanced row sampling

---

That's all because PHP is parsed in your server (side) and the HTML generated by it go to client browser and get renderized...

---

I'm not a fan of unions, but sometimes they have their places... :)

SELECT type, id, content FROM ( 
SELECT 'M' AS type, id, 0 AS reply_id, content FROM MESSAGE
UNION
SELECT 'R' AS type, message_id AS id, id AS reply_id, content FROM MESSAGE_COMMENT) a 
ORDER BY id, reply_id

returns

+------+----+--------------------------------------------+
| type | id | content                                    |
+------+----+--------------------------------------------+
| M    | 15 | this is a message with no comments         |
| M    | 16 | this is another message with many comments |
| R    | 16 | comment1                                   |
| R    | 16 | comment2                                   |
| R    | 16 | comment3                                   |
| M    | 17 | this is another message with no comments   |
+------+----+--------------------------------------------+

NB: The second SELECT in the UNION could easily be reworked with an INNER JOIN to the MESSAGE table if orphaned message_comments were a concern.