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.
Advertisement
Answer
Dipping into some Arel statements, I was able to achieve what I needed here by using a NOT EXISTS
clause along with JOIN
ing 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') ) ) )