ActiveRecord query through multiple joins

Question

I have a schema like this.

managers  
    has_many :emails  
    has_many :stores

emails  
    belongs_to :manager

stores  
    belongs_to :manager  
    belongs_to :region

regions  
    has_many :stores  
    has_many :readings

readings  
    belongs_to :regions

I want to get readings for a manager. In SQL I would do something like this.

SELECT * FROM managers  
    JOIN stores ON stores.manager_id = managers.id  
    JOIN regions ON stores.region_id = regions.id  
    JOIN readings ON readings.region_number = regions.number  
WHERE  
    manager.name = 'John Smith'  
AND  
    regions.number = '1234567'
LIMIT 100

I can't figure out how to do this in activerecord. I have been trying to make sense of http://guides.rubyonrails.org/active_record_querying.html and http://guides.rubyonrails.org/association_basics.html but it's not sinking in. I think I just need to see it from a different view point.

I was thinking I would be accessing the data like this but I think I just don't understand how it works.

managers.name  
managers.stores.name  
managers.stores.regions.readings.limit(10)

I have been having to so something like this which is a whole lot uglier.

managers.first.stores.first.regions.first.readings.limit(10)

Solution 1

Consider the following models (and use of has_many through) :

class Reading < ActiveRecord::Base
  belongs_to :region,
    inverse_of: :readings
end

class Region < ActiveRecord::Base
  has_many :readings,
    inverse_of: :region

  has_many :stores,
    inverse_of: :region    
end

class Store < ActiveRecord::Base
  belongs_to :region,
    inverse_of: :stores

  belongs_to :manager,
    inverse_of: :stores
end

class Manager < ActiveRecord::Base
  has_many :stores,
    inverse_of: :region

  has_many :emails,
    inverse_of: :manager

  has_many :regions,
    through: :stores

  has_many :readings,
    through: :regions
end

class Email < ActiveRecord::Base
  belongs_to :manager,
    inverse_of: :emails
end

Now your question is a little ambiguous because you say you want to obtain readings for a manager but your SQL doesn't select readings at all and also prescribes a region.

Assuming you want all Reading's matching a given Manager and Region:

@readings = Reading.joins(region: { stores: :manager }).where(
  manager: { name: 'John Smith' },
  region: { id: 1234567 })

Assuming you also want to eager load regions, stores and managers to avoid 1+N queries:

@readings = Reading.includes(region: { stores: :manager }).where(
  manager: { name: 'John Smith' },
  region: { id: 1234567 })

Assuming you have a managers name and want both their details and readings:

@manager = Manager.where(name: 'John Smith').first!
@readings = manager.readings

All of the above query examples return ActiveRecord::Relation's which can be further chained with where conditions, or joins, limit, group, having and order etc

You should also consider the differences of joins, includes, preload, eager_load and references methods. There is a brief on them here I would alos encourage you to read docs, guides and blogs about Arel as it supports joins and aliasing too.

After using ActiveRecord in anger for a while now I have come to the conclusion that Sequel/SequelModel is a much better DB/ORM than ActiveRecord. No disrespect to the developers but I've found Sequel is a better tool. Arel has thin documentation for years now and ActiveRecord/Arel have failings in a number of areas such as join conditions, control of join types and eager loading, unions, intersections, recursive queries, trees/adjacency lists, and many other SQL features that Sequel covers.

Since you appear to be just starting out with AR you may wish to instead start out with Sequel than struggle with weaknesses and the frustrations of ActiveRecord querying including the disjointed use of AR and Arel, Relations vs Associations and query composition oddities, it goes on and on. There is nothing more frustrating than knowing the SQL you want but ActiveRecord/Arel conspire to stop you so you're forced to use the touted escape route and 'just use SQL string fragment' and you get back a result that can't be chained, but the rest of your code expects a Relation! (eg paged results)

Solution 2

I wanted to comment on the comment from user1757006 Oct 4 '13 at 14:39, but I don't have enough points.. Anyway, this addresses deeper nesting scenarios. I have added planet and country models to show how the syntax works when needing to chain additional models.

@readings = Reading.includes(planet: [ country: [ region: [ {stores:
:manager}]]]).where(
manager: { name: 'John Smith' },   
region: {id: 1234567 })

Solution 3

Assuming managers is a model name

 managers.find(:all,
               :joins=>" JOIN stores ON stores.manager_id = managers.id  
                         JOIN regions ON stores.region_id = regions.id  
                         JOIN readings ON readings.region_number = regions.number"
               :conditions=>"manager.name = 'John Smith' AND regions.number = '1234567'"
               :limit=>100)

Solution 4

try something like this:

managers.joins(stores: {regions: :readings}).where('managers.name = ? AND regions.number = ?', 'John Smith', '1234567').limit(100) 

View more solutions

Comments

[newUserNameHere]

Pasting an image of the schema made your question easier to follow. Will start doing the same in my DB questions. Nice touch!

[user1757006]

This looks like what I read in the rails guides but the syntax was not explained there. Could you explain what is happening in the joins() method. In the rails guides they say Category.joins(posts: [{comments: :guest}, :tags]) but I it does not make sense enough to me to extend it. For instance what if I had to chain 2 more tables, 3 more, 4 more etc. Also how do I bring in another table that is not part of this chain like the emails table?

[pyRabbit]

I found this to be very helpful in the interim until Rails 5 is released with the ActiveRecord #or method. Thank you.

[DivinesLight]

Wow, never knew that you could chain joins like that. Still the object ActiveRecord returns is very tricky and you have to believe that the key exists to access data :P

[MSC]

Absolutely. Where on earth is this documented?

[CTS_AE]

This is exactly the Intro to Active Record 201 that I needed, thanks! This will definitely take me far with determining how to get my SQL mindset into activerecord land. Protip for anyone in my boat: use .to_sql or .explain to see the SQL query and how it matches up with what you'd expect.