尝试构建一个允许多个收件人的线程消息传递模块。为此，我有一个收件人表，每行包含一个message_id和recipient_id。 下面我创建了本质上是用户消息的列表。代码有效，但由于创建连接的方式，如果有多个收件人，我会多次收到相同的消息消息，这使得无法限制唯一消息的数量。
是否可以对邮件数量添加限制，但仍检索收件人列表，可能在语句中选择另一个选项？
我想语句下方的 php 准确地显示了我想要实现的目标。
$query = mysql_query(
"SELECT
messages.message_id,
messages.message_parent_id,
messages.message_from_id ,
messages.message_from_email,
messages.message_subject,
messages.message_body,
messages.message_sent_datetime,
u_from.forename AS from_forename,
u_from.surname AS from_surname,
m_recipients.recipient_user_id AS recipient_user_id,
u_to.forename AS to_forename,
u_to.surname AS to_surname
FROM messages
INNER JOIN users AS u_from ON messages.message_from_id = u_from.user_id
INNER JOIN message_recipients AS m_recipients ON messages.message_id = m_recipients.recipient_message_id
INNER JOIN users AS u_to ON m_recipients.recipient_user_id = u_to.user_id
WHERE m_recipients.recipient_user_id = 1 OR messages.message_from_id = 1
ORDER BY messages.message_sent_datetime DESC
");
$messages = [];
while($row=mysql_fetch_array($query)) {
if (array_key_exists($row['message_id'], $messages)) {
$messages[$row['message_id']]['to'][] = $row['to_forename'] . ' ' . $row['to_surname'];
}
else {
$messages[$row['message_id']] = [
'to' => [$row['to_forename'] . ' ' . $row['to_surname']],
'from' => $row['from_forename'] . ' ' . $row['from_surname'],
'subject' => $row['message_subject'],
'body' => $row['message_body'],
'datetime' => $row['message_sent_datetime']
];
}
}
下面将返回以逗号分隔的 from 和 to 名称的消息。
这足够好吗？
SELECT
messages.message_id, messages.message_parent_id, messages.message_from_id ,
messages.message_from_email, messages.message_subject, messages.message_body,
messages.message_sent_datetime,
GROUP_CONCAT(CONCAT(u_from.forename, ' ', u_from.surname)) as from,
GROUP_CONCAT(CONCAT(u_to.forename, ' ', u_to.surname)) as to
FROM messages
INNER JOIN users AS u_from ON messages.message_from_id = u_from.user_id
INNER JOIN message_recipients AS m_recipients ON messages.message_id = m_recipients.recipient_message_id
INNER JOIN users AS u_to ON m_recipients.recipient_user_id = u_to.user_id
WHERE m_recipients.recipient_user_id = 1 OR messages.message_from_id = 1
GROUP BY messages.message_id
ORDER BY messages.message_sent_datetime DESC
你可能想玩GROUP_CONCAT（DISTINCT ）