如何依据关联订阅表条件，查询数据库中当前用户可访问的帖子

前提约定 #

• 假设posts.type_id = 1为对所有订阅用户可见，posts.type_id = 2为仅付费订阅用户可见，可根据你实际业务的枚举值调整判断条件
• 假设@current_user_id为当前登录用户的ID变量，使用时替换为你项目中实际传参的用户ID即可

查询SQL #

SELECT DISTINCT p.*
FROM posts p
LEFT JOIN user_subscriptions us 
  ON us.creator_id = p.user_id 
  AND us.user_id = @current_user_id
WHERE 
  -- 可选规则：当前用户自己发布的帖子默认可见，不需要订阅
  p.user_id = @current_user_id
  OR (
    -- 已订阅该帖子的创作者
    us.id IS NOT NULL
    AND (
      -- 帖子对所有订阅用户开放
      p.type_id = 1
      -- 帖子仅对付费订阅用户开放，且当前用户是付费订阅
      OR (p.type_id = 2 AND us.is_paid_subscription = 1)
    )
  )
ORDER BY p.id DESC;

逻辑说明 #

1. 先关联订阅表，匹配当前用户对帖子创作者的订阅关系
2. 两种情况的帖子会被返回：
   • 当前用户自己发布的所有帖子（不需要订阅，不需要满足可见性规则，如果不需要这个规则可以直接删掉对应WHERE条件）
   • 当前用户已订阅创作者，且帖子可见性要求和订阅等级匹配
3. 加DISTINCT是为了避免极端场景下重复数据返回，如果你表结构没有冗余关联数据可以去掉。

性能优化建议 #

给相关表加联合索引可以大幅提升查询效率：

• user_subscriptions 表添加 (user_id, creator_id, is_paid_subscription) 联合索引
• posts 表添加 (user_id, type_id) 联合索引

内容的提问来源于stack exchange，提问作者Alexxosipov