Skip to content
Advertisement

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

group_member.rb

newsletter.rb

newsletter/author.rb

newsletter/story.rb

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:

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.

Advertisement

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:

That generates SQL that looks something like this:

User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement