Rails: Convert a complex SQL query to Arel or ActiveRecord

Question

In a Rails app I'm working on, I've set up a scope on a Newsletter Author model which uses a complex query I've figured out in raw SQL like so:

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])
}

This does exactly what I need it to, which, along with some context (models below) is this: A Group has Members and Newsletters. Some of those members can be authors for a given newsletter. Those authors can write stories for the newsletter, and each story can be in Draft, Submitted (for publication), Published or Retracted state. Draft stories may or may not be assigned to a specific newsletter, but stories in all other states are assigned to a single newsletter. This query identifies authors assigned to a specific newsletter who have drafts written but no submitted stories to that newsletter.

I'd love to figure out how to translate this to Arel or Active Record statements for better consistency with elsewhere in the codebase. I cannot quite wrap my head around all the details of making that happen, particularly around setting up the subquery correctly.

EDIT: Changed the query to sanitize the newsletter_id argument, as per @Eyeslandic's suggestion.

EDIT 2: Here are the models I'm working with here, condensed for clarity. Remember, the scope above is on the Newsletter::Author model:

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

Answer 1

While we can certainly build your query in Arel, after reviewing your SQL a bit it looks like it would actually be much cleaner to simply build this using the AR API instead.

The following should produce the exact query you are looking for (sans "newsletter_stories"."author_id" = "group_members"."id" because this is already implied by the join)

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