No way to use includes in ActiveRecord to avoid n+1 queries in views?

[ncancelliere]

It doesn't seem there is a way to avoid n+1 queries when using acts_as:

$ store = Store.includes(:products).where(id: 1).first
=>
Store Load SELECT stores SELECT 'stores'.* FROM 'stores' WHERE 'id' = 1
Products Load SELECT products SELECT 'products'.* FROM 'products' WHERE 'products'.'actable_id' = 1
$

Note it's not preloading Book or Pen.

If you're showing an index page that uses data from "specific" (or the actual classes that are acts_as :product) it generates additional queries, and there is no way to seemingly avoid this.

For example you might have workflow states that in the Pen class are defined as "full", "half-full", "empty"; and in Book "on_shelf", "on_loan" ... so you have different workflow_actions specific to their type. So you could imagine an index view where you want to show the status of each product and so having to call product.specific.workflow_state on each one results in a query (which is bad).

Is there something I'm missing? You should be able to preload everything so there are a minimum set of queries (e.g. pull the store, all products, and all related pens and books). But you cannot really say includes(products: [:specific]) or even includes(products: [:book, :pen]) (assuming you knew all the actable classes for product) because Product doesn't have any real association to Book or Pen.

Is there some other strategy around dealing with this, which I'd imagine is a common use case?

[jayelkaake]

+1 something to avoid the n+1 queries would be great!

[dja]

+1

[xzhflying]

+1

[psusmars]

I believe the include statement you're looking for would be something like: includes(products: [:actable])

[ncancelliere]

That still results in n+1 queries.

>> Service.includes(:actable)
  SQL (2.1ms) SELECT `services`.* FROM `services`  WHERE `services`.`deleted_at` ...(snip)... AND `stores`.`id` IN (1, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72)
  Service Load (1.8ms)  SELECT  `services`.* FROM `services`  WHERE `services`.`deleted_at` IS NULL AND `services`.`actable_id` = 1 AND `services`.`actable_type` = 'Store' LIMIT 1
  Service Load (2.1ms)  SELECT  `services`.* FROM `services`  WHERE `services`.`deleted_at` IS NULL AND `services`.`actable_id` = 62 AND `services`.`actable_type` = 'Store' LIMIT 1
  Service Load (1.8ms)  SELECT  `services`.* FROM `services`  WHERE `services`.`deleted_at` IS NULL AND `services`.`actable_id` = 63 AND `services`.`actable_type` = 'Store' LIMIT 1
  Service Load (0.9ms)  SELECT  `services`.* FROM `services`  WHERE `services`.`deleted_at` IS NULL AND `services`.`actable_id` = 64 AND `services`.`actable_type` = 'Store' LIMIT 1
  Service Load (1.9ms)  SELECT  `services`.* FROM `services`  WHERE `services`.`deleted_at` IS NULL AND `services`.`actable_id` = 65 AND `services`.`actable_type` = 'Store' LIMIT 1
  Service Load (2.1ms)  SELECT  `services`.* FROM `services`  WHERE `services`.`deleted_at` IS NULL AND `services`.`actable_id` = 66 AND `services`.`actable_type` = 'Store' LIMIT 1
  Service Load (0.8ms)  SELECT  `services`.* FROM `services`  WHERE `services`.`deleted_at` IS NULL AND `services`.`actable_id` = 67 AND `services`.`actable_type` = 'Store' LIMIT 1
  Service Load (3.0ms)  SELECT  `services`.* FROM `services`  WHERE `services`.`deleted_at` IS NULL AND `services`.`actable_id` = 68 AND `services`.`actable_type` = 'Store' LIMIT 1
  Service Load (0.8ms)  SELECT  `services`.* FROM `services`  WHERE `services`.`deleted_at` IS NULL AND `services`.`actable_id` = 69 AND `services`.`actable_type` = 'Store' LIMIT 1
  Service Load (0.7ms)  SELECT  `services`.* FROM `services`  WHERE `services`.`deleted_at` IS NULL AND `services`.`actable_id` = 70 AND `services`.`actable_type` = 'Store' LIMIT 1
  Service Load (0.7ms)  SELECT  `services`.* FROM `services`  WHERE `services`.`deleted_at` IS NULL AND `services`.`actable_id` = 71 AND `services`.`actable_type` = 'Store' LIMIT 1
  Service Load (2.0ms)  SELECT  `services`.* FROM `services`  WHERE `services`.`deleted_at` IS NULL AND `services`.`actable_id` = 72 AND `services`.`actable_type` = 'Store' LIMIT 1
  SQL (2.8ms)  SELECT `domains`.*, `domains`.`id` AS ...(snip)... IN (1, 2)
  Service Load (0.8ms)  SELECT  `services`.* FROM `services`  WHERE `services`.`deleted_at` IS NULL AND `services`.`actable_id` = 1 AND `services`.`actable_type` = 'Domain' LIMIT 1
  Service Load (1.1ms)  SELECT  `services`.* FROM `services`  WHERE `services`.`deleted_at` IS NULL AND `services`.`actable_id` = 2 AND `services`.`actable_type` = 'Domain' LIMIT 1

[nuclearpidgeon]

Are you guys running v1.0.8 of the gem? (released Jan 27 2016)

A change in commit 95e66e733a0ee3b14daaa617cf58ded21f2e3c31 may have fixed this issue. (I was having similar issues that are now resolved on the latest version)

[ncancelliere]

We are using 1.0.2. I'll try to upgrade and see what it does.

[nuclearpidgeon]

Another option is to use the standard Rails ActiveRecord relation helpers on your top-level acts_as model, with scopes around the actable_type field. All the .includes() code in Rails is built to leverage those. So in the Store/Products/Pen example:

class Store < ActiveRecord::Base
  has_many :products
end

class Product < ActiveRecord::Base
  actable
  belongs_to :store

  belongs_to :pen,
    -> { where actable_type: 'Pen' },
    foreign_key: "actable_id"

  belongs_to :book,
    -> { where actable_type: 'Book' },
    foreign_key: "actable_id"
end

class Pen < ActiveRecord::Base
  acts_as :product

  has_one :product,
    -> { where actable_type: 'Pen' },
    foreign_key: "actable_id"
end

class Book < ActiveRecord::Base
  acts_as :product

  has_one :product,
    -> { where actable_type: 'Book' },
    foreign_key: "actable_id"
end

Just watch out for dependent: :destroy if you're using that - you may need to duplicate it on the has_one/belongs_to methods.

We added this kind of code in a project that uses this Gem around the same time we updated to 1.0.8 and have been able to get efficient queries working most of the time now. It's a bit of a workaround, but until the gem itself has proper includes() support, it gets the job done.

(It may not even be possible to add proper support for includes() - depends on if/how you can extend its behaviour from Rails)

[ncancelliere]

I had tried something like what you have above but when I use .specific on the product (actable) it gives a column error when trying to query for the acts_as.