CODEWITHSUNDEEP
sqlruby-on-railsactiverecord
Michael Bester
Michael Bester

Reputation: 325

Filtering an association by missing records in Rails

In a Rails application I'm working on, I've got a few different models associated thusly (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 :subscriptions, inverse_of: :group_member, class_name: 'Newsletter::Subscriber'

  scope :subscribed_to, ->(newsletter_id) { joins(:subscriptions).merge(Newsletter::Subscriber.where(["newsletter_id = ?", newsletter_id])) }
  scope :not_subscribed_to, ->(newsletter_id) { where.missing(:subscriptions) }
end

newsletter.rb

class Newsletter < ApplicationRecord
  acts_as_tenant :group

  has_many :subscribers, inverse_of: :newsletter
end

newsletter/subscriber.rb

class Newsletter::Subscriber < ApplicationRecord
  acts_as_tenant :group

  belongs_to :newsletter, inverse_of: :subscribers
  belongs_to :group_member, class_name: 'GroupMember', inverse_of: :subscriptions
end

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

What I'm trying to do (unsuccessfully, so far), is find out which members in a group are NOT subscribed to a specific newsletter that is associated with the group.

I can find out the members that DO have a subscription using the following scope on the GroupMember object:

  scope :subscribed_to, ->(newsletter_id) { joins(:subscriptions).merge(Newsletter::Subscriber.where(["newsletter_id = ?", newsletter_id])) }

That allows me to query, for instance, current_group.members.subscribed_to(current_group.newsletters.first.id).

However, I'm not sure how to negate that and get the the opposite of that set of members. That is, members NOT subscribed to that specific newsletter. The :not_subscribed_to scope I currently have defined isn't cutting it because it doesn't take into account which newsletter I'm referring to.

Upvotes: 0

Views: 617

Answers (1)

max
max

Reputation: 102016

Given the variable newsletter_id.

One alternative is to use WHERE NOT EXISTS(...) with a subquery:

Member
  .where(
    'NOT EXISTS(
       SELECT 1 FROM "subscriptions" 
       WHERE "subscriptions"."member_id" = "members"."id" 
       AND   "subscriptions"."newsletter_id" = ?
    )', newsletter_id
  )

Translated into Arel:

Member.where(
  Subscription.select('1')
              .where(
                Subscription.arel_table[:member_id].eq(Member.arel_table[:id])
              ).where(
                newsletter_id: newsletter_id
              ).arel.exists.not
)

Or group, count and having:

Member.group(:id)
      .left_joins(:subscriptions)
      .where(subscriptions: { newsletter_id: newsletter_id })
      .having(Subscription.arel_table[:id].count.eq(0))

Upvotes: 1

Related Questions