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.
精彩评论