Mysql查询查找下相同订单的 friend 和 friend 的 friend

Question

我有如下3个表格

用户

- id integer primary_key
- user_name 

friend

- id autoincrement primary_key 
- user1 integer foreign_key(User.id)
- user2 integer foreign_key(User.id)

订单

- id integer primary_key autoincrement
- product_id integer 
- by_user foreign_key(User.id) 

我已获得用户 ID 和 product_id。

我必须编写一个查询，该查询将向我提供该用户下单购买相同产品的所有 friend 以及该用户下单购买相同产品的 friend 的所有 friend 。

如何在 mysql 中编写这样的查询？

---

示例数据的解释

用户

id    |   name
------------------
1     |   peter   
2     |   sam
3     |   xang
4     |   aisha
5     |   rita
6     |   mojo
7     |   raj
8     |   ben

friend

(如果id为1和2的user是好友那么可以存储为user1 = 1 and user2 = 2 OR user1 = 2 and user2 = 1，都表示用户之间有关系)

id          user1        user2 
---------------------------------------------
1      |     1       |     2      
2      |     2       |     3      
3      |     3       |     4      
4      |     4       |     2      
5      |     5       |     6      
6      |     6       |     8      
7      |     8       |     1   

订单

id         product_id           by_user 
-----------------------------------------------
1      |    100        |          2        
2      |    100        |          3       
3      |    100        |          4       
4      |    100        |          5       
5      |    100        |          7       
6      |    101        |          5       
7      |    102        |          6       
8      |    103        |          1          

所以提供的数据是:

用户 = 1 和产品 = 100

我们要做的:

id = 1 的用户的 friend 是 [2,8](我们称它为 f1)

f1 中用户的 friend = [1,3,4,6](我们称它为 f2)

现在从订单表中为 product_id = 100 下订单并在 f1+f2 中具有用户 ID 的用户 ID，即 [2,8,1,3,4,6]

所以这里的结果应该是:

[2,3,4]

Answer 1

SELECT DISTINCT u.id, u.name
FROM
    Friends f1
    INNER JOIN Friends f2
    ON f1.user1 = f2.user1
    OR f1.user1 = f2.user2
    OR f1.user2 = f2.user1
    OR f1.user2 = f2.user2
    INNER JOIN Orders o
    ON (f1.user1 = o.by_user
    OR f1.user2 = o.by_user
    OR f2.user1 = o.by_user
    OR f2.user2 = o.by_user)
    AND o.product_id = 100
    INNER JOIN Users u
    ON o.by_user = u.id
WHERE   
    (f1.user1 = 1
    OR f1.user2 = 1)

此答案已更新以说明 friend 的 friend 并为您添加解释。

• 在 Friends 表中查询包含用户 1 的任何记录，这将为您提供用户 1 以及他们的直接 friend 。
• 使用 user1 = user1, 2 to 2 1 to 2 2 to 1 的所有组合将 SELF JOIN 添加到 Friends 表中作为 OR 以获得所有 Friends of Friends
• 获取与您现在拥有的 4 个用户列的任意组合相关的所有订单，并通过 product_id = 100 限制结果
• 您现在有重复的结果，因此选择 DISTINCT 以获得每个用户 1 条记录。