Rails: Finding records where a nested association is empty

In a Rails application I’m working on, I’ve got a few different models associated like this (condensed for clarity):

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

Given the above associated models, here’s the framework I’m working within:

• Each Newsletter has n Authors (Group Members) and n Newsletters.
• Each Group Member can author multiple stories for a given newsletter.
• Each story is one of theses status states: draft, submitted, or published
• A draft story may or may not be associated with a Newsletter
• A submitted or published story is associated with a Newsletter

I’d like to find out which Authors for a given newsletter have NO stories with a draft or submitted status.

Given newsletter_id, I can find out the members that DO have a draft or submitted story with a query like this:

  Newsletter.find(newsletter_id).authors
    .joins(group_member: :stories)
    .where(stories: {status: [:draft, :submitted]})
    .distinct

However, I’m not sure how to negate that and get the the opposite of that set of authors. That is, authors for a given newsletter who DON’T have draft or submitted stories. Whether or not they have published stories should make no difference.

EDIT

I asked a similar question a few months ago about identifying records where records of an associated model did not exist. I think that’s a very similar approach for what I need to do here, but I haven’t quite cracked how to apply that answer to this question due to the nested association of GroupMember (as Newsletter::Author) -> Newsletter -> Newsletter::Story

A pure SQL answer here would also be enlightening.

Answer

Dipping into some Arel statements, I was able to achieve what I needed here by using a NOT EXISTS clause along with JOINing Newsletter Stories to the GroupMembers, filtering the stories which don’t have a status of draft or submitted. Given the newsletter_id, here’s what worked for me:

Newsletter::Author
  .where(newsletter_id: newsletter_id)
  .where(
    GroupMember.select("1")
      .where(
        Newsletter::Story.arel_table[:author_id].eq(Newsletter::Author.arel_table[:group_member_id])
      )
      .joins(:stories)
      .where.not(
        Newsletter::Story.arel_table[:status].not_eq_all([:draft, :submitted])
      )
      .arel.exists.not
    )

That generates SQL that looks something like this:

SELECT * FROM newsletter_authors
  WHERE newsletter_authors.newsletter_id = [newsletter_id]
  AND NOT (
    EXISTS (
      SELECT 1 FROM group_members
      INNER JOIN newsletter_stories ON newsletter_stories.author_id = group_members.id
      WHERE newsletter_stories.author_id = newsletter_authors.group_member_id
      AND NOT (
        (newsletter_stories.status != 'draft' AND newsletter_stories.status != 'submitted')
      )
    )
  )