How to avoid n+1 queries when using polymorphic associations?

Question

I have 4 models, let's say:

class Photo < ActiveRecord::Base
  belongs_to :photoable, polymorphic: true
end

class User < ActiveRecord::Base
  has_one :photo, as: :photoable
end    

class Company < ActiveRecord::Base
  has_one :photo, as: :photoable
  has_many :products
end

class Products < ActiveRecord::Base
  belongs_to :company
end

So, the query Photo.all.includes(:photoable) works.

But if I use Photo.all.includes(photoable: :products) only works if all loaded photos belongs to Company. If the relation contains photos of users and companies, this error is raised:

ActiveRecord::ConfigurationError (Association named 'products' was not found; perhaps you misspelled it?):

This occurs because user hasn't relationship with products.

Is there any way to eager load users and companies with products for a relation of photos?

EDIT:

This question isn't duplicated of Eager load polymorphic. As I commented below, in this question I want to do eager load for polymorphic associations which has different associations(one has products and the other don't). In that question, the OP uses wrong names for table names.

possible duplicate of [Eager load polymorphic](http://stackoverflow.com/questions/16123492/eager-load-polymorphic) — phoet, Jan 27 '15 at 15:01
@phoet in this question I want to do eager load for polymorphic associations which has different associations(one has products and the other don't). In that question, the OP uses wrongs names for table names. — Rodrigo, Jan 27 '15 at 15:27
I haven't tested it, but does `Photo.all.includes(:user, company: :products)` work? — ptd, Jan 27 '15 at 15:44
No @ptd, because `photo` does not have `user` or `company` association. It has `photoable` association — Rodrigo, Jan 27 '15 at 16:03
i don't think that this is supported by rails. you would have to create n joins, one for each type of "photoable". — phoet, Jan 29 '15 at 10:38
@Rodrigo, did you ever find a solution? I'm having the same problem... — David, Sep 14 '15 at 23:16

score 3 · Answer 1 · answered Jan 23 '20 at 18:18

Rails 6 added support for this case, now nested associations are only preloaded if they are defined for the object, you can even mix associations from different classes:

Photo.all.includes(photoable: [:products, :posts, :some_undefined])

Full runnable example:

require "bundler/inline"

gemfile(!ENV['SKIP_INSTALL']) do
  source "https://rubygems.org"
  # rails 5 gives the error, run with RAILS5=1 to see
  gem "activerecord", ENV['RAILS5'] && "~>5.0" || "~>6.0"
  gem "sqlite3"
end

require "active_record"
require "minitest/autorun"
require "logger"

ActiveRecord::Base.establish_connection(adapter: "sqlite3", database: ":memory:")
ActiveRecord::Base.logger = Logger.new(STDOUT)

ActiveRecord::Schema.define do
  create_table(:photos){|t| t.references :photoable, polymorphic: true }
  create_table(:users)
  create_table(:companies)
  create_table(:products){|t| t.references :company }
  create_table(:posts){|t| t.references :user }
end

# --- Models:
class Photo < ActiveRecord::Base
  belongs_to :photoable, polymorphic: true
end

class User < ActiveRecord::Base
  has_one :photo, as: :photoable
  has_many :posts
end    

class Company < ActiveRecord::Base
  has_one :photo, as: :photoable
  has_many :products
end

class Product < ActiveRecord::Base
  belongs_to :company
end

class Post < ActiveRecord::Base
  belongs_to :user
end

# --- Test:

class SomeTest < Minitest::Test
  def setup
    2.times{    
      Company.create!.tap{|company|
        company.products.create!
        company.create_photo!
      }
      User.create!.tap{|user|
        user.posts.create!
        user.create_photo!
      }  
    }
  end

  def test_association_stuff
    rel = Photo.all.includes(photoable: [:products, :posts, :some_undefined])
    arr = rel.to_a # fetch all records here

    ActiveRecord::Base.logger.extend(Module.new{
      define_method(:debug){|msg|  raise "should not log from now on" }
    })

    assert_kind_of(Product, arr.first.photoable.products.first)
    assert_kind_of(Post, arr.last.photoable.posts.first)
  end
end

score 2 · Answer 2 · answered Apr 14 '17 at 15:39

You may add specific associations to Photo model:

class Photo < ActiveRecord::Base
  belongs_to :photoable, polymorphic: true

  belongs_to :user, -> { where(photoable_type: 'User' ) }, foreign_key: :photoable_id
  belongs_to :company, -> { where(photoable_type: 'Company' ) }, foreign_key: :photoable_id
end

After that you may preload nested specific associations:

photos = Photo.all.includes(:photoable, company: :products)

With access to records like this:

photos.each do |photo|
  puts photo.photoable.inspect
  puts photo.company.products.inspect if photo.photoable_type == "Company"
end

This approach loads company association twice, but does not do n+1 queries.

score 1 · Answer 3 · answered May 04 '17 at 15:08

1

class Products < ActiveRecord::Base
  belongs_to :company
end

Should be

class Product < ActiveRecord::Base
  belongs_to :company
end

And then it will work.

answered May 04 '17 at 15:08

Billy Ferguson

1,429
11
23

score 0 · Answer 4 · answered Feb 09 '15 at 09:49

0

I have same issues, and have found a solution.

You can call the eager load after the find :

photos = Photo.all.includes(:photoable)
photos_with_products_association = photos.select{|p| p.photoable.is_a?(Company)}
ActiveRecord::Associations::Preloader.new.preload(
  photos_with_products_association,
  :products
)

... or more generic

photos_with_products_association = photos.select do |p|
  p.class.reflections.keys.include?(:products)
end

answered Feb 09 '15 at 09:49

pierallard

3,326
3
21
48

1

This solution selects all photos from database before loading the `products`. So, I'm looking for an way to do this in only one query with JOINS. – Rodrigo Feb 11 '15 at 19:44
1

2 queries isn't bad... Avoiding n+1 should be your concern. – Oskar Holmkratz Nov 05 '15 at 15:37
When bazillion photos are returned in the first query - having N+1 may better, if there's pagination – Vasfed Jan 23 '20 at 17:06

score 0 · Answer 5 · answered Mar 23 '15 at 17:15

It's quite strange your are joining companies to your photos and not the other way around. Why would you do that? And do you really need those company products in the view or controller?

SELECT * from photos
LEFT JOIN users AS u ON u.id = photos.photoable_id AND 
                        photos.photoable_type = 'User'
LEFT JOIN companies AS c ON c.id = photos.phottoable_id AND
                        photos.photoable_type = 'Company'
LEFT JOIN products AS p ON p.company_id = c.id

How to avoid n+1 queries when using polymorphic associations?

5 Answers5