SQL查询线程消息 [英] SQL Querying for Threaded Messages

问题描述

我的网站有一个消息功能，一个用户可以给另一个消息发送消息。消息支持线程 - 父消息可能有任意数量的子级，但只有一级。

消息表如下所示：

 消息
  -  Id（PK，自动递增int）
  -  UserId（FK，Users.Id）
  -  FromUserId （FK，Users.Id）
  -  ParentMessageId（FK到Messages.Id）
  -  MessageText（varchar 200）
  

我想在每个'父'消息的页面上显示消息，然后是折叠的子消息视图。

我可以使用GROUP BY子句或类似的结构在一个查询中检索父消息和子消息吗？现在，我仅检索父消息，然后遍历它们并为每个消息执行另一个查询以获取所有相关的子消息。

我希望得到像这样的消息：

 父母1 
孩子1 
孩子2 
孩子3 
父母2 
 Child1 
 Parent3 
 Child1 
 Child2 
  

解决方案您可以使用临时ID来订购邮件。如果消息是Parent，那么临时ID将等于ID，否则临时ID将等于ParentMessageID。然后，您只需要通过临时ID来订购

  SELECT消息。*，
 CASE当ParentMessageId为NULL时则为Id ELSE ParentMessageId END AS tempId 
 FROM消息
 ORDER BY tempId 
  

编辑

如果您需要前10条记录，您可以先获取ID并运行查询

  SELECT消息。*，
 CASE WHEN ParentMessageId IS NULL THEN Id ELSE ParentMessageId END AS tempId 
 FROM消息
 WHERE Messages.tempId IN（SELECT Messages.Id 
 FROM Messages 
 WHERE ParentMessageId IS NULL 
 LIMIT 10 
 ORDER BY Messages.Id）
 ORDER BY tempId 
  
 
 
 
通过这种方式，您只能收到前10封邮件中的邮件和相应的孩子。
 
My site has a messaging feature where one user may message another. The messages support threading - a parent message may have any number of children but only one level deep.

The messages table looks like this:
Messages
 - Id (PK, Auto-increment int)
 - UserId (FK, Users.Id)
 - FromUserId (FK, Users.Id)
 - ParentMessageId (FK to Messages.Id)
 - MessageText (varchar 200)
I'd like to show messages on a page with each 'parent' message followed by a collapsed view of the children messages.

Can I use the GROUP BY clause or similar construct to retrieve parent messages and children messages all in one query? Right now I am retrieving parent messages only, then looping through them and performing another query for each to get all related children messages.

I'd like to get messages like this:
Parent1
 Child1
 Child2
 Child3
Parent2
 Child1
Parent3
 Child1
 Child2

解决方案
You can use a temporary ID to order the messages. If the message is a Parent then the temporary ID will be equal to the ID, else the temporary ID will be equal to the ParentMessageID. Then you just need to order by the temporary ID
SELECT Messages.*, 
CASE WHEN ParentMessageId IS NULL THEN Id ELSE ParentMessageId END AS tempId 
FROM Messages
ORDER BY tempId
Edit

If you want the first 10 records you could get the Ids first and then run the query
SELECT Messages.*, 
CASE WHEN ParentMessageId IS NULL THEN Id ELSE ParentMessageId END AS tempId 
FROM Messages
WHERE Messages.tempId IN (SELECT Messages.Id 
                      FROM Messages
                      WHERE ParentMessageId IS NULL
                      LIMIT 10
                      ORDER BY Messages.Id )
ORDER BY tempId
This way you only get the messages and respective childs from the first 10 messages.

                        
这篇关于SQL查询线程消息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！