title: Rails: Finding records where a nested association is empty
body: <p>In a Rails application I'm working on, I've got a few different models associated like this (condensed for clarity):</p>
  <p><code>group.rb</code></p>
  <pre><code>class Group &lt; ApplicationRecord
    has_many :members, class_name: 'GroupMember'
    has_many :newsletters
  end
  </code></pre>
  <p><code>group_member.rb</code></p>
  <pre><code>class GroupMember &lt; ApplicationRecord
    belongs_to :group
    has_many :authorships, inverse_of: :group_member, class_name: &quot;Newsletter::Author&quot;
    has_many :stories, inverse_of: :author, class_name: &quot;Newsletter::Story&quot;
  end
  </code></pre>
  <p><code>newsletter.rb</code></p>
  <pre><code>class Newsletter &lt; ApplicationRecord
    has_many :authors, inverse_of: :newsletter
    has_many :stories
  end
  </code></pre>
  <p><code>newsletter/author.rb</code></p>
  <pre><code>class Newsletter::Author &lt; ApplicationRecord
    belongs_to :newsletter, inverse_of: :authors
    belongs_to :group_member, class_name: &quot;GroupMember&quot;, inverse_of: :authorships
  end
  </code></pre>
  <p><code>newsletter/story.rb</code></p>
  <pre><code>class Newsletter::Story &lt; ApplicationRecord
    belongs_to :newsletter, inverse_of: :stories, optional: true
    belongs_to :author, inverse_of: :stories, class_name: &quot;GroupMember&quot;
  
    enum status: {draft: &quot;draft&quot;, submitted: &quot;submitted&quot;, published: &quot;published&quot;}, _default: &quot;draft&quot;
  end
  </code></pre>
  <p>Given the above associated models, here's the framework I'm working within:</p>
  <ul>
  <li>Each Newsletter has <em>n</em> Authors (Group Members) and <em>n</em> Newsletters.</li>
  <li>Each Group Member can author multiple stories for a given newsletter.</li>
  <li>Each story is one of theses status states: <strong>draft</strong>, <strong>submitted</strong>, or <strong>published</strong></li>
  <li>A draft story may or may not be associated with a Newsletter</li>
  <li>A submitted or published story is associated with a Newsletter</li>
  </ul>
  <p>I'd like to find out which Authors for a given newsletter have NO stories with a <strong>draft</strong> or <strong>submitted</strong> status.</p>
  <p>Given <code>newsletter_id</code>, I can find out the members that DO have a draft or submitted story with a query like this:</p>
  <pre><code>  Newsletter.find(newsletter_id).authors
      .joins(group_member: :stories)
      .where(stories: {status: [:draft, :submitted]})
      .distinct
  </code></pre>
  <p>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.</p>
  <p><strong>EDIT</strong></p>
  <p>I asked <a href="https://stackoverflow.com/questions/68212648/filtering-an-association-by-missing-records-in-rails">a similar question</a> 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 <code>GroupMember (as Newsletter::Author)</code> -&gt; <code>Newsletter</code> -&gt; <code>Newsletter::Story</code></p>
  <p>A pure SQL answer here would also be enlightening.</p>