Rails查询指定newsletter下无草稿/待提交story的关联作者实现

解法1：子查询取反（写法更直观，适合中小数据量） #

# 先定位目标Newsletter
newsletter = Newsletter.find(newsletter_id)

# 子查询：查询当前Newsletter下有draft/submitted状态稿件的作者ID
invalid_author_ids = newsletter.authors
  .joins(group_member: :stories)
  .where(
    stories: {
      status: [:draft, :submitted],
      newsletter_id: newsletter.id # 必须限定当前Newsletter，避免误判作者在其他Newsletter下的稿件
    }
  )
  .distinct
  .select(:id)

# 取反得到目标作者集合
target_authors = newsletter.authors.where.not(id: invalid_author_ids)

解法2：左连接匹配空值（性能更优，适合大数据量场景） #

利用左连接特性，没有匹配到符合条件的稿件时关联表字段为NULL，直接筛选即可：

newsletter = Newsletter.find(newsletter_id)

target_authors = newsletter.authors
  .left_joins(group_member: :stories)
  .where(
    stories: {
      status: [:draft, :submitted],
      newsletter_id: newsletter.id
    }
  )
  .where(stories: { id: nil })
  .distinct

纯SQL解法 #

SELECT DISTINCT newsletter_authors.*
FROM newsletter_authors
-- 关联作者对应的群组成员
INNER JOIN group_members ON newsletter_authors.group_member_id = group_members.id
-- 左连接匹配当前Newsletter下的draft/submitted状态稿件
LEFT JOIN newsletter_stories ON 
  newsletter_stories.author_id = group_members.id
  AND newsletter_stories.status IN ('draft', 'submitted')
  AND newsletter_stories.newsletter_id = ? -- 替换为目标Newsletter的ID
WHERE 
  newsletter_authors.newsletter_id = ? -- 替换为目标Newsletter的ID
  AND newsletter_stories.id IS NULL

注意事项 #

必须在查询稿件时加上newsletter_id的限定条件，否则会将作者在其他Newsletter下的draft/submitted状态稿件纳入判断，导致结果不符合预期。该逻辑默认忽略published状态的稿件，完全符合需求。

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