Rails Activerecord query through three associations?

0
votes

Company has_many :agents, Agent belongs_to :company.

Agent has_many :comments, Comment belongs_to both :agent and :business.

Business has_many :comments.

What ActiveRecord query finds all the businesses with a comment written by an agent of the company?

I can get all the comments for all the agents of a company:

@company.agents.joins(:comments)

And using that query I can pluck all the business_ids that are commented on by company agents:

@company.agents.joins(:comments).pluck(:business_id)

(So if nothing else works I could get the desired list of businesses using a second query into Business given an array of IDs.)

However I cannot seem to extend the association chain in a single query to includes the Businesses, eg, a query that finds the Business record for each business commented upon by company agents, eg something like:

@company.agents.joins(:comments).joins(:business) # Can't join 'Agent' to association named 'businesses'

EDIT:

Tried @company.agents.joins(:comments => :business) as suggested by Jon in the comments. That works if doing a .count() or a .pluck().

If need to also query by Business fields, that can be done with:

@company.agents.joins(:comments => :business).where(:businesses => {:account_status => :active})
1
ruby-on-railsactiverecordassociations
It looks like you're putting the second join onto the agents scope. Have you tried doing something like this: @company.agents.joins(:comments => :business)? – Jon
Bingo, yes, thank you. I thought I'd tried that, perhaps I got the plural/singular incorrect earlier. – jpw
although @company.agents.joins(:comments => :business) still does not permit then appending additional queries such as where("business.status = ?", 'active') whcih gives the error "missing FROM-clause entry for table "business"" – jpw
.where(:businesses => {:account_status => :active}) is the key – jpw
Jon if want to re-post as an answer i'll happily accept it – jpw

1 Answers

1
votes

You're adding the second join query onto the agents scope. You'll need to use something like the following:

@company.agents.joins(:comments => :business)

If you need to add conditions against the business you can do so as follows:

@company.agents.joins(:comments => :business).where(:businesses => {:account_status => :active})

This way it correctly chains the join queries for you and adds the conditions to the appropriate tables.