如何在SQL/SQLite中设计好友关系表并查询好友所持物品

实现逻辑说明 #

• 你当前的好友表设计中，每一组双向好友关系对应唯一friendship_id，两个用户会在user_friendship表中各存一条关联该ID的记录
• 双方均同意好友申请后，两条记录的status统一设置为1（你可根据自己的实际状态约定调整该数值）
• 查询目标为：输入当前登录用户名，输出该用户所有好友持有的物品列表

可用查询语句 #

-- 把两个?都替换为你要查询的当前用户的用户名即可
SELECT DISTINCT item.*
FROM item
JOIN user_friendship uf_friend ON item.owner = uf_friend.username
JOIN user_friendship uf_me ON uf_friend.friendship_id = uf_me.friendship_id
WHERE 
  uf_me.username = ? 
  AND uf_me.status = 1
  AND uf_friend.status = 1
  AND uf_friend.username != ?

查询逻辑拆解 #

1. 先通过uf_me筛选出当前用户名下所有状态为已同意的好友关系ID
2. 用同一个好友关系ID关联uf_friend表，拿到该关系对应的另一个用户（即当前用户的好友）的用户名
3. 用好友用户名关联item表的owner字段，即可查询到所有好友持有的物品
4. 加入DISTINCT是为了避免重复数据返回

额外优化提示：你目前设计的friendship表仅存储主键，没有其他业务字段，如果后续不需要扩展好友关系的公共属性（比如申请时间、好友分组备注），可以直接删除该表，所有好友逻辑都放在user_friendship中即可，不会影响当前查询的运行。

内容的提问来源于stack exchange，提问作者Just A Question