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!

Adrian Serafin

Adrian Serafin
I'm experienced Ruby developer. I love PostgreSQL, Redis and optimizing performance. I strongly believe in using right tool for the job and short feedback loops. How can I help you?

group_concat as an alternative for array_agg

For quite some time I have been hardcore PostgreSQL user. I like to leveragedatabase capabilities when possible to get faster response ti...… Continue reading

React router on Amazon S3

Published on March 23, 2016