用于从所有用户的朋友那里检索消息的SQL查询

Question

我有一个Twitter克隆网站的3个数据库设置。

表'users'：

email | firstname | lastname | hash_password

链接表'friends'：

email | friend

这将列出所有用户及其朋友。友谊是单向的(就像Twitter的“关注”/“关注”)。friends表是一个链接表，用于列出所有用户的好友。所以friends.user和friends.friend都是users.email的外键。

表'messages'：

timestamp | user | content

从用户及其朋友那里检索所有消息的SQL查询是什么？

我试过了：

SELECT
  'timestamp', 
  user, 
  content 
FROM
  messages
  INNER JOIN friends ON messages.user = friends.friend
ORDER BY 'timestamp' DESC;

这似乎正确地加入了他们，但我如何才能只获得特定用户的(电子邮件地址)朋友的消息。现在，它只返回所有消息。

谢谢。

Answer 1

timestamp的引号应该带反引号，否则MySQL将假定为字符串字面值。

否则，要同时返回来自用户和所有朋友的消息，您可以使用UNION。一半返回用户的消息，另一半返回好友的消息。您需要将用户的电子邮件添加到您的加入条件中：

/* First part of the UNION returns friends messages */
SELECT
  `timestamp`, 
  user, 
  content 
FROM
  messages 
  /* The join ON clause specifies the main user email */
  INNER JOIN friends 
    ON messages.user = friends.friend
       AND friends.email = 'email@example.com'
UNION ALL
/* Other part of the UNION just gets all messages for the main user by email addr */
SELECT
  `timestamp`,
  user,
  content
FROM
  messages
WHERE user = 'email@example.com'
/* ORDER BY applies to everything */
ORDER BY `timestamp` DESC;

如果您想在这里连接users info (firstname/lastname)，最简单的方法是将整个内容包装在子查询中并进行连接。

SELECT
  users.*,
  messages_sub.content,
  messages_sub.`timestamp`
FROM
  users
  JOIN (/* The entire thing from above */) AS messages_sub ON users.email = messages_sub.user
ORDER BY `timestamp`

它也可以使用您想要查找的文字电子邮件地址和朋友列表的UNION来完成，只产生一个外部查询。这有点棘手，但最终可能会更快。在这里引入users表中的其他列也不会那么令人困惑。我将添加这些名称：

SELECT
  `timestamp`,
  user,
  firstname,
  lastname,
  content
FROM
  messages
  INNER JOIN (
    /* Union query produces a list of email addrs - 
       the person you're looking for plus all his friends
       Starts with string literal for the main user
    */
    SELECT 'email@example.com' AS email
    UNION 
    /* Plus all his friends into one list joined against messages */
    SELECT friend AS email FROM friends WHERE email = 'email@example.com'
  ) user_and_friends ON messages.user = user_and_friends.email
  /* Join against the users table for name info */
  INNER JOIN users ON user_and_friends.email = users.email
ORDER BY `timestamp` DESC