CODEWITHSUNDEEP
sqlruby-on-railsrubypostgresqlactiverecord
Dylan Karr
Dylan Karr

Reputation: 3574

Find records through two intermediate models in rails?

I'm having some trouble trying to fetch some models via SQL in rails and I was wondering if anyone knows of a good solution for this particular problem. Basically, these are what my classes look like:

class SubscriberList < ActiveRecord::Base
  has_many :subscriptions
end

class Subscription < ActiveRecord::Base
  has_many :messages
  belongs_to :subscription_list
end

class Announcement < ActiveRecord::Base
  has_many :messages
end

class Message < ActiveRecord::Base
  belongs_to :subscription
  belongs_to :announcement
end

Now, I want to access all the Announcements for a SubscriptionList excluding duplicates. Is this possible? Can it be done with a single SQL query or is that just wishful thinking?

For example here's what I want:

class SubscriptionList < ActiveRecord::Base
  def announcements
    Announcements.joins(:messages).where(message: { subscription: {subscription_list: self} })
  end
end

Upvotes: 0

Views: 55

Answers (1)

gotva
gotva

Reputation: 5998

I think your idea is correct in general. Try this variant

Announcements.
  joins(messages: {subscription: :subscription_list}).
  where(subscription_lists: {id: self.id})

# opposite
SubscriptionList.
  joins(subscriptions: {messages: :announcement}).
  where(announcements: {id: self.id})

Notes: * these queries may return duplicates - so uniq can be added to them * self can be omitted (I wrote it to show that this is id of instance and avoid missunderstanding)

Upvotes: 1

Related Questions