ActiveRecord eager load multiple belongs_to associations

Question

The 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 select queries:

SELECT * FROM cars WHERE cars.id = ?

This is essentially the N+1 problem.

Ideally, I'd like it to generate just one query of the form

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

Possible solution

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

Another possible solution

Another possible solution would be to manually load the associations like so:

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 have tested it, and it works.

class Person
  include EagerLoader
end

Person.find(2).eager_load(:favorite_car, :business_car, :home_car)

However, this still does not help you when you want to do things like

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

For example, on the person index page. This reduces the number of queries from 3N+1 to 4, but only 2 are actually needed.

Are there any better solutions to this problem?

How does a favorite car differ rom a business car (in the model sense, that is)? When you go to access car data for a Person, do you typically need all three bits (the fave, biz and home cars)? — craig.kaminsky, Feb 18 '14 at 20:52
favourite_car, business_car and home_car are all just instances of Car, so there is no difference in the models. Almost all of the time, I need to access all three bits or none at all. — Cameron Martin, Feb 18 '14 at 20:57
Sorry ... did not ask that well. What differentiates a fave car from a home car (is there a 'boolean' or 'type' column that differentiates these models)? In other words, how do you know that a Car is the favorite or business or home? (I really am asking for a reason ... not to be annoying :)). — craig.kaminsky, Feb 18 '14 at 21:09
Is there a reason a person does not have_one instead of belongs_to? Seems like this would be an easier set up. What does your table structure look like? — engineersmnky, Feb 18 '14 at 21:42
@craig.kaminsky There is a favourite_car_id, business_car_id, and home_car_id foreign key in the people table, as per activerecord conventions for belongs_to associations. — Cameron Martin, Feb 18 '14 at 21:45
Ok, thanks ... just now back to SO and read @franksort's reply, which works well for meeting the single query req. — craig.kaminsky, Feb 18 '14 at 22:23
I have updated the question with a half-solution, but it still does more queries than necessary when doing `includes()` eager-loading — Cameron Martin, Feb 19 '14 at 00:39
@CameronMartin Where did you pull that `EagerLoader` module from? Is it already a part of the Rails core? — franksort, Feb 19 '14 at 00:40
No, it's something I just made to eager load the associations. It only half solves my problem though, see the bottom of my edit. — Cameron Martin, Feb 19 '14 at 00:54
@engineersmnky Do you mean a `has_one :through => ` with a join table for each of favourite_car, business_car, home_car? I can't have a has_one on Person with a belongs_to on Car, because Car needs to has_many people_as_favourite, people_as_business etc — Cameron Martin, Feb 19 '14 at 01:05
"Possible Solution" as you list above is my choice of solutions. You are adding a table, but you are also removing 2 columns and making the data structure less restrictive. The possibility for expansion in the future is good (what if I want to add 'my 2nd favourite car'). Then the joining table will work well. — Rots, Feb 19 '14 at 01:52

franksort · Answer 1 · 2014-02-18T22:10:44.980

Try this:

> 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

app/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

app/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 will automagically use people as the plural of person. So when you create a Person model, it will create a people database table.

That looks nice but it's doing three LEFT OUTER JOINs. Wouldn't be better to do two queries? Load Person and Load three cars. — André Barbosa, Feb 18 '14 at 22:06
@AndréBarbosa The question asked for a solution with one query. — franksort, Feb 18 '14 at 22:07
I'm not criticizing, just wondering if there is a solution with better performance. — André Barbosa, Feb 18 '14 at 22:11
@AndréBarbosa I think there is definitely a better solution. I would probably just go with your two queries, make sure my tables were properly indexed, then work on caching and cache invalidation for the pages containing this data. — franksort, Feb 18 '14 at 22:16
I posted a possible solution, will update the answer after I've tested it — Cameron Martin, Feb 18 '14 at 23:08

score 4 · Accepted Answer · answered Sep 05 '14 at 21:01

There is a great post about manual Eager load.

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

I guess this is what you have been 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

Hesham · Answer 3 · 2014-02-19T14:47:37.080

0

I'd personally create a join table. Having said that, here's another option: it is possible to define relation 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)

edited Feb 19 '14 at 14:47

answered Feb 19 '14 at 01:16

Hesham

2,327
2
20
24

This doesn't work. It tries to execute the query `SELECT "cars".* FROM "cars" WHERE "cars"."person_id" IN (1, 2, 3)`, where 1,2,3 are the person ids. – Cameron Martin Feb 19 '14 at 01:49
That's weird. I modified the answer. Can you try it again? – Hesham Feb 19 '14 at 14:49

ActiveRecord eager load multiple belongs_to associations

The problem

Possible solution

Another possible solution

3 Answers3