preloading associations with dynamic condition in rails

N+1 queries issue

The N+1 query issue is common performance problem in Rails applications.

To better understand it let’s consider typical e-commerce application where we’re dealing with products with prices in multiple currencies.

class Product < ActiveRecord :: Base has_many :prices end class Price < ActiveRecord :: Base belongs_to :product validates :currency_code , presence: true end

When we want to display all products and show price in USD we can do something like this

Product . all . each do | product | product . prices . where ( currency_code: 'USD' ). cents end

Above code will result in 1 query to fetch all products and 1 query for each product to fetch price in USD

select * from products ; select * from prices where product_id = 1 and currency_code = 'USD' select * from prices where product_id = 2 and currency_code = 'USD' select * from prices where product_id = 3 and currency_code = 'USD' -- ...

This will make your database unhappy and your application slow.

Eager loading to the rescue

The easiest way to fix N+1 queries is to use association’s eager loading provided by ActiveRecord (here is excellent article describing all 3 available options and I strongly recommend to read it!)

Product . all . includes ( :prices ). each do | product | product . prices . find { | price | price . currency_code == 'USD' }. cents end

Now our queries will look like this

select * from products ; select * from prices where product_id in ( 1 , 2 , 3 ,...)

Great, no more N+1 issue! On the second look though we’re still loading more data than we actually need.

We just need to show prices in USD and yet we also loaded all other currencies.

Multiple associations

In ActiveRecord we can pass where condition to association. That means we can have usd_prices , eur_prices , etc. and use that in includes .

Here’s refactored example:

class Product < ActiveRecord :: Base has_many :prices has_many :usd_prices , -> { where ( currency_code: 'USD' ) } has_many :eur_prices , -> { where ( currency_code: 'EUR' ) } # ... end Product . all . includes ( :usd_prices ). each do | product | product . usd_prices . first . cents end

select * from products ; select * from prices where currency_code = 'USD' and product_id in ( 1 , 2 , 3 ,...)

This solution is almost ideal since we don’t have N+1 issue and we only load data that we’re going to use. But It’s not flexible: if we want to handle new currency we have to add association, reload code and so on.

This could be acceptable for currency because we won’t be adding new ones very often but there might be situations that would require more dynamic approach.

Preloading with dynamic scope

We cannot pass dynamic conditions to ActiveRecord’s associations so we need to dig a little deeper and discover ActiveRecord::Associations::Preloader api.

# Eager loads the named associations for the given Active Record record(s). # # In this description, 'association name' shall refer to the name passed # to an association creation method. For example, a model that specifies # <tt>belongs_to :author</tt>, <tt>has_many :buyers</tt> has association # names +:author+ and +:buyers+. # # == Parameters # +records+ is an array of ActiveRecord::Base. This array needs not be flat, # i.e. +records+ itself may also contain arrays of records. In any case, # +preload_associations+ will preload the all associations records by # flattening +records+. # # +associations+ specifies one or more associations that you want to # preload. It may be: # - a Symbol or a String which specifies a single association name. For # example, specifying +:books+ allows this method to preload all books # for an Author. # - an Array which specifies multiple association names. This array # is processed recursively. For example, specifying <tt>[:avatar, :books]</tt> # allows this method to preload an author's avatar as well as all of his # books. # - a Hash which specifies multiple association names, as well as # association names for the to-be-preloaded association objects. For # example, specifying <tt>{ author: :avatar }</tt> will preload a # book's author, as well as that author's avatar. # # +:associations+ has the same format as the +:include+ option for # <tt>ActiveRecord::Base.find</tt>. So +associations+ could look like this: # # :books # [ :books, :author ] # { author: :avatar } # [ :books, { author: :avatar } ] ActiveRecord :: Associations :: Preloader . new . preload ( records , associations , preload_scope = nil )

The third option - preload_scope - is not documented, but it provides us with the ability to filter prices with currency.

products = Product . all . to_a ActiveRecord :: Associations :: Preloader . new . preload ( products , :prices , Price . where ( currency_code: 'USD' )) products . each do | product | product . prices . first . cents end

Generated queries are identical to the ones from prev. example

select * from products ; select * from prices where currency_code = 'USD' and product_id in ( 1 , 2 , 3 ,...)

but since we’re passing the currency dynamically we can add new one on the fly!