活动流/提要的SQL查询问题

Question

我正在构建一个允许用户相互推荐音乐的应用程序，并且在构建查询时遇到了问题，该查询将返回包括用户自己以及用户的任何朋友的推荐"流"。这是我的表结构:

建议

ID     Sender     Recipient    [other columns...]
--     ------     ---------    ------------------
r1     u1         u3           ...
r2     u3         u2           ...
r3     u4         u3           ...

用户

ID      Email      First Name   Last Name     [other columns...]
---     -----      ----------   ---------     ------------------
u1      ...        ...          ...           ...
u2      ...        ...          ...           ...
u3      ...        ...          ...           ...
u4      ...        ...          ...           ...

的关系

ID      Sender     Recipient     Status       [other columns...]
---     ------     ---------     --------     ------------------
rl1     u1         u2            accepted     ...
rl2     u3         u1            accepted     ...
rl3     u1         u4            accepted     ...
rl4     u3         u2            accepted     ...

因此，对于用户'u4'(谁是'u1'的朋友)，我想查询与u4相关的推荐'流'。此流将包括发送方或接收方为u4的所有推荐，以及发送方或接收方为u1(好友)的所有推荐。

这是目前为止我的查询:

 SELECT * FROM recommendations
  WHERE recommendations.sender IN 
    ( SELECT sender FROM relationships WHERE recipient='u4' AND status='accepted' 
      UNION 
      SELECT recipient FROM relationships WHERE sender='u4' AND status='accepted') 
 OR recommendations.recipient IN 
    ( SELECT sender FROM relationships WHERE recipient='u4' AND status='accepted' 
      UNION 
      SELECT recipient FROM relationships WHERE sender='u4' AND status='accepted')
 UNION
 SELECT * FROM recommendations
 WHERE recommendations.sender='u4' OR recommendations.recipient='u4'
 GROUP BY recommendations.id
 ORDER BY datecreated DESC

这似乎工作，据我所见(我不是SQL专家)。它返回推荐表中与给定用户"相关"的所有记录。但是，我现在也有麻烦从Users表中获取数据。Recommendations表包含发件人和收件人的ID(外键)，但我还想获得每个人的姓和名。我想我需要某种JOIN，但我不知道如何进行，正在寻求帮助。(而且，如果有人在我当前的查询中看到任何需要改进的地方，我洗耳恭听。)

谢谢!

Answer 1

好吧，相信我想明白了。必须创建用户表的2个别名，以便我可以从每个别名中提取。下面是工作查询:

SELECT recommendations.*, user1.firstname AS 'senderFirstName', user1.lastname AS 'senderLastName', user2.firstname AS 'recipientFirstName', user2.lastname AS 'recipientLastName'
FROM (recommendations, users)
INNER JOIN users AS user1 ON (user1.id=recommendations.sender)
INNER JOIN users AS user2 ON (user2.id=recommendations.recipient)
WHERE recommendations.sender IN (SELECT sender FROM relationships WHERE recipient='4' AND status='accepted' UNION SELECT recipient FROM relationships WHERE sender='4' AND status='accepted') OR recommendations.recipient IN (SELECT sender FROM relationships WHERE recipient='4' AND status='accepted' UNION SELECT recipient FROM relationships WHERE sender='4' AND status='accepted')
UNION
SELECT recommendations.*, user1.firstname AS 'senderFirstName', user1.lastname AS 'senderLastName', user2.firstname AS 'recipientFirstName', user2.lastname AS 'recipientLastName'
FROM (recommendations, users)
INNER JOIN users AS user1 ON (user1.id=recommendations.sender)
INNER JOIN users AS user2 ON (user2.id=recommendations.recipient)
WHERE recommendations.sender='4' OR recommendations.recipient='4'
GROUP BY recommendations.id
ORDER BY date created DESC

如果有人发现任何错误或改进的地方，我将非常感谢。