Joining nested associations, multiple level

Question

For those of you that know rails and sql well, I'm looking for some good information you could point me to. My query is very similar to the 'Joining Nested Associations' example in this section - http://guides.rubyonrails.org/active_record_querying.html#using-array-hash-of-named-associations

My models (abbreviated) are as follows,

User has_many :products    # User is 'great-grandparent'
Product has_many :posts    # Product is grandparent #1
Event has_many :posts      # Event is grandparent #2
Post belongs_to :event
Post belongs_to :product
Post has_many :orders      # Post is parent
Order belongs_to :post     # Order is great-grandchild, grandchild, & child

I want to collect the orders from an event for a user (the seller), and here's my best crack at it.

class Order < ActiveRecord::Base
  def self.collect_for_seller_and_event(user_id, event_id)
    self.joins(:post => [{:product => :user }, :event]).where(:post => [{:product => {:user_id => user_id}}, {:event_id => event_id}])
  end

What what should this join look like?

Should I break this up into scopes in the various models on the daisy chain?

Just to show that I have a basic understanding here, I've been able to get my first working nested join table going (I'm pretty excited about this accomplishment)

BuyerFeedback 
belongs_to :order
def self.from_past_events
   self.joins(:order => {:post => :event}).where(:order => {:post => {:event => {:date.lt => Date.today}}})
end

score 1 · Answer 1 · edited Apr 19 '16 at 02:38

Your idea to break this up into scopes is good. But you may not have to daisy chain as much as you'd think because you can merge in scopes from other models into any associated model. Here's a great article on ARel#merge, which makes this wonderful thing possible: http://ben.hoskings.net/2012-07-04-arel-merge-a-hidden-gem

This way, you can break your thinking up into smaller chunks. So, start with Post and create a scope that gets you all associated products given a user. Then create a scope that chains that scope onto one that gets you all posts given an event. Then just merge this scope into your User scope.

Product
  scope :for_user, ->(user) { where(user_id: user) }  # The passed in `user` can be an instance of user or an id

Post
  scope :for_product, ->(product) { where(product_id: product) }
  scope :for_product_given_user, ->(product, user) { for_product(product).merge(Product.for_user(user)) }
  scope :for_product_given_user_and_event, ->(product, user, event) { for_product_given_user(product, user).where(event_id: event) }

I'm not sure I'm quite on the right track here given I can't test... but maybe this can get you started. The idea is to break up the responsibilities into manageable chunks and let ARel do the crazy SQL merge work.

Joining nested associations, multiple level

1 Answers1