ActiveRecord eagerly loads multiple association links

Question

Problem

I have the following ActiveRecord model:

class Person belongs_to :favourite_car, class_name: 'Car' belongs_to :business_car, class_name: 'Car' belongs_to :home_car, class_name: 'Car' end 

When I want to access all three of these associations, it generates three requests:

 SELECT * FROM cars WHERE cars.id = ? 

This is essentially an N + 1 problem.

Ideally, I would like it to generate only one form request

 SELECT * FROM cars WHERE cars.id IN (?, ?, ?) 

Possible Solution

I could move this to the has_many :through => :join_table with a column in the connection table to indicate the type of association, and then use includes([:join_table, :cars]) to load the associations. However, in this case it reduces only 3 queries to 2 and introduces an additional table.

Another possible solution

Another possible solution would be to manually download the associations as follows:

 module EagerLoader def eager_load(*associations) reflections = associations.map { |association| self.class.reflections[association.to_sym] } raise 'Not all are valid associations' if reflections.any?(&:nil?) reflections.group_by { |association| association.klass }.each do |klass, reflections| load_associations(klass, reflections) end self end private def load_associations(klass, reflections) primary_key = klass.primary_key ids = reflections.map { |reflection| public_send(reflection.foreign_key) } records = klass.where(id: ids) reflections.each_with_index do |reflection, i| record = records.find do |record| record.public_send(primary_key) == ids[i] end public_send("#{reflection.name}=", record) end end end 

I tested it and it works.

 class Person include EagerLoader end Person.find(2).eager_load(:favorite_car, :business_car, :home_car) 

However, this still will not help you when you want to do something like

 Person.includes(:favourite_car, :business_car, :home_car) 

For example, on a person’s index page. This reduces the number of requests from 3N + 1 to 4, but actually only 2 is required.

Are there any better solutions to this problem?

Answer 1

There is an excellent article on manual downloads.

http://mrbrdo.wordpress.com/2013/09/25/manually-preloading-associations-in-rails-using-custom-scopessql/

I think this is what you were looking for:

 owners = People.all association_name = :photos owners.each do |owner| record = whatever_you_want association = owner.association(association_name) association.target = record association.set_inverse_instance(record) end 

Answer 2

Try the following:

 > person_id = 1 > person = Person.includes(:favorite_car, :business_car, :home_car).where("people.id" = ?", person_id).references(:favorites_car, :business_car, :home_car) > person[0].favorite_car 

application / models / person.rb

 class Person < ActiveRecord::Base # columns: id, name, favorite_car_id, business_car_id, home_car_id belongs_to :favorite_car, class_name: 'Car' belongs_to :business_car, class_name: 'Car' belongs_to :home_car, class_name: 'Car' end 

application / models / car.rb

 class Car < ActiveRecord::Base # columns: id, name has_many :people end 

Proof that this works:

 > Person.all Person Load (0.3ms) SELECT "people".* FROM "people" => #<ActiveRecord::Relation [#<Person id: 1, favorite_car_id: 1, business_car_id: 2, home_car_id: 3, name: "Frankie", created_at: "2014-02-18 21:51:58", updated_at: "2014-02-18 21:53:34">]> > Car.all Car Load (0.3ms) SELECT "cars".* FROM "cars" => #<ActiveRecord::Relation [#<Car id: 1, name: "Mazda", created_at: "2014-02-18 21:52:16", updated_at: "2014-02-18 21:52:16">, #<Car id: 2, name: "Honda", created_at: "2014-02-18 21:52:20", updated_at: "2014-02-18 21:52:20">, #<Car id: 3, name: "BMW", created_at: "2014-02-18 21:52:24", updated_at: "2014-02-18 21:52:24">]> > Person.includes(:favorite_car, :business_car, :home_car).where("people.id = ?", 1).references(:favorite_car, :business_car, :home_car) SQL (0.4ms) SELECT "people"."id" AS t0_r0, "people"."favorite_car_id" AS t0_r1, "people"."business_car_id" AS t0_r2, "people"."home_car_id" AS t0_r3, "people"."name" AS t0_r4, "people"."created_at" AS t0_r5, "people"."updated_at" AS t0_r6, "cars"."id" AS t1_r0, "cars"."name" AS t1_r1, "cars"."created_at" AS t1_r2, "cars"."updated_at" AS t1_r3, "business_cars_people"."id" AS t2_r0, "business_cars_people"."name" AS t2_r1, "business_cars_people"."created_at" AS t2_r2, "business_cars_people"."updated_at" AS t2_r3, "home_cars_people"."id" AS t3_r0, "home_cars_people"."name" AS t3_r1, "home_cars_people"."created_at" AS t3_r2, "home_cars_people"."updated_at" AS t3_r3 FROM "people" LEFT OUTER JOIN "cars" ON "cars"."id" = "people"."favorite_car_id" LEFT OUTER JOIN "cars" "business_cars_people" ON "business_cars_people"."id" = "people"."business_car_id" LEFT OUTER JOIN "cars" "home_cars_people" ON "home_cars_people"."id" = "people"."home_car_id" WHERE (people.id = 1) => #<ActiveRecord::Relation [#<Person id: 1, favorite_car_id: 1, business_car_id: 2, home_car_id: 3, name: "Frankie", created_at: "2014-02-18 21:51:58", updated_at: "2014-02-18 21:53:34">]> 

Important Note: Rails automatically uses people as the plural of person . Therefore, when you create the person model, it will create a people database table.

Answer 3

I personally created a connection table. Having said that, here is another option: you can determine the relationship based on SQL queries:

 class Person belongs_to :favourite_car, class_name: 'Car' belongs_to :business_car, class_name: 'Car' belongs_to :home_car, class_name: 'Car' has_many :cars, class_name: 'Car', :finder_sql => %q( SELECT DISTINCT cars.* FROM cars WHERE cars.id IN (#{c_ids}) ) def c_ids [favourite_car_id, business_car_id, home_car_id].compact.uniq.join(',') end end 

Then you can do Person.includes (: cars)