php - 同一 MYSQL 查询中的父消息和子消息

Question

我想从上表中提取一个列表，其中包含 parent_id = 0 且 sender_id 或 receiver_id 等于我的登录 ID 的所有消息。

Table:Messages
id | parent_id | sender_id | receiver_id | subject | message | read

这很简单，但我无法弄清楚如何计算 WHERE parent_id = 0 的消息的所有子消息。

我试过这个:

$my_messages = mysql_query("
  SELECT

messages.id, messages.sender_id, messages.receiver_id, messages.subject, messages.message, messages.minute,
 messages.hour, messages.day, messages.month, messages.year, COUNT(*) as 'mcount'
    FROM messages 
    LEFT JOIN messages AS mchild ON mchild.parent_id = messages.id 
    WHERE(messages.sender_id='$login_session' or messages.receiver_id='$login_session') 
    and messages.parent_id = '0'
    ORDER BY messages.year DESC, messages.month DESC, messages.day DESC, messages.hour DESC, messages.minute DESC

");

我无法理解的另一件事是我如何计算每个 parent_id=0 的所有子+父消息 WHERE read=1 if sender_id = $login_id 或 read=1 if receiver_id = $login_id

这是一个例子:

Table:Messages
id | parent_id | sender_id | receiver_id | subject | message | read
 1 |         0 | Paul      | John        | Test    | Test    | 0
 2 |         0 | Paul      | Chris       | Test    | Test    | 0
 3 |         1 | john      | Paul        | Test    | Test    | 0
 4 |         1 | Paul      | John        | Test    | Test    | 1
 5 |         1 | John      | John        | Test    | Test    | 0
 6 |         0 | Paul      | Jack        | Test    | Test    | 0

输出:

ID:1 - 4 messages (1 parent+ 3 children), $unread=1 because read = 1 for  ID:4 wich is a child for ID:1
ID:2 - 1 message
ID:6 - 1 message

Answer 1

使用自连接:

SELECT m1.id,
       COUNT(*) AS childCount
FROM messages m1
INNER JOIN
messages m2
    IN m1.id = m2.parent_id
WHERE '$login_session' IN(m1.sender_id,m1.receiver_id)
  AND m1.id = 0 -- you can remove this condition if you want all parents

GROUP BY m1.id

通常 COUNT 函数与 GROUP BY 一起使用，在这种情况下，组是每个父级。但是由于您只希望 parent_id 为零，我将其添加到 WHERE 子句中，这将过滤掉除这一个之外的所有组。如果您想了解更多信息，可以将其删除。