sql - Rails：将复杂的 SQL 查询转换为 Arel 或 ActiveRecord

Question

在我正在开发的 Rails 应用程序中，我在 Newsletter Author 模型上设置了一个范围，该模型使用了我在原始 SQL 中找到的复杂查询，如下所示：

scope :without_submissions_for, lambda { |newsletter_id|
  query = <<~SQL.squish
    SELECT * FROM "newsletter_authors"
    WHERE "newsletter_authors"."discarded_at" IS NULL
    AND "newsletter_authors"."newsletter_id" = :newsletter_id
    AND "newsletter_authors"."group_member_id" IN (
      SELECT DISTINCT "group_members"."id" FROM "group_members"
      INNER JOIN "newsletter_stories" ON "newsletter_stories"."author_id" = "group_members"."id"
      WHERE "newsletter_stories"."author_id" = "group_members"."id"
      AND "newsletter_stories"."status" = 'draft'
      AND NOT (
        EXISTS (
          SELECT 1 FROM "newsletter_stories"
          WHERE "newsletter_stories"."author_id" = "group_members"."id"
          AND "newsletter_stories"."status" = 'submitted'
        )
      )
    );
  SQL
  find_by_sql([query, newsletter_id: newsletter_id])
}

这正是我需要的，连同一些上下文（下面的模型）是这样的：一个组有成员和时事通讯。其中一些成员可以是给定时事通讯的作者。这些作者可以为时事通讯撰写故事，每个故事都可以处于Draft、Submitted（用于发布）、Published或Retracted状态。草稿故事可能会或可能不会分配给特定的时事通讯，但所有其他州的故事都会分配给单个时事通讯。此查询标识分配给特定时事通讯的作者，这些作者撰写了草稿但没有向该时事通讯提交故事。

我很想弄清楚如何将其转换为 Arel 或 Active Record 语句，以便与代码库中的其他地方更好地保持一致。我无法完全理解实现这一点的所有细节，尤其是在正确设置子查询方面。

编辑：newsletter_id根据@Eyeslandic 的建议，更改了查询以清理参数。

编辑 2：这里是我在这里使用的模型，为了清楚起见进行了浓缩。请记住，上面的范围在Newsletter::Author模型上：

group.rb

class Group < ApplicationRecord
  has_many :members, class_name: 'GroupMember'
  has_many :newsletters
end

group_member.rb

class GroupMember < ApplicationRecord
  belongs_to :group
  has_many :authorships, inverse_of: :group_member, class_name: "Newsletter::Author"
  has_many :stories, inverse_of: :author, class_name: "Newsletter::Story"
end

newsletter.rb

class Newsletter < ApplicationRecord
  has_many :authors, inverse_of: :newsletter
  has_many :stories
end

newsletter/author.rb

class Newsletter::Author < ApplicationRecord
  belongs_to :newsletter, inverse_of: :authors
  belongs_to :group_member, class_name: "GroupMember", inverse_of: :authorships
end

newsletter/story.rb

class Newsletter::Story < ApplicationRecord
  belongs_to :newsletter, inverse_of: :stories, optional: true
  belongs_to :author, inverse_of: :stories, class_name: "GroupMember"

  enum status: {draft: "draft", submitted: "submitted", published: "published"}, _default: "draft"
end

标签： sqlruby-on-railsarel

Answer 1

虽然我们当然可以在 Arel 中构建您的查询，但在稍微查看您的 SQL 之后，看起来使用 AR API 来构建它实际上会更简洁。

以下应该产生您正在寻找的确切查询（"newsletter_stories"."author_id" = "group_members"."id"因为连接已经暗示了这一点）

class Newsletter::Author < Application Record
  belongs_to :newsletter, inverse_of: :authors
  belongs_to :group_member, class_name: "GroupMember", inverse_of: :authorships
  scope :without_submissions_for, ->(newsletter_id) {
    group_members = GroupMember
         .select(:id)
         .joins(:stories)
         .where(newsletter_stories: {status: 'draft'})
         .where.not( 
           Newsletter::Story
            .select(1)
            .where(status: 'submitted')
            .where(Newsletter::Story.arel_table[:author_id].eq(GroupMember.arel_table[:id]))
            .arel.exists
         ).distinct
    where(discarded_at: nil, newsletter_id: newsletter_id, group_member_id: group_members)
  }
end