Avoid N + 1 queries for association

scoped_association.md

Preload scoped associations

Rails provide different ways to load associations from database to avoid N + 1 query being fired.

I have two models User and Post with following associations:

# app/models/user.rb
class User < ActiveRecord::Base
  has_many :posts
end

# app/models/post.rb
class Post < ActiveRecord::Base
  belongs_to :user
  scope :published, -> { where(published: true) }
end

Now if you loop over the association you will see N + 1 queries being fired

2.0.0-p645 :020 > 2.0.0-p645 :064 > User.all.map do |user| 
2.0.0-p645 :021 >   [user.name, user.posts.map(&:title).join(', ')] 
2.0.0-p645 :022 > end
  User Load (0.3ms)  SELECT "users".* FROM "users"
  Post Load (0.4ms)  SELECT "posts".* FROM "posts" WHERE "posts"."user_id" = ?  [["user_id", 1]]
  Post Load (0.2ms)  SELECT "posts".* FROM "posts" WHERE "posts"."user_id" = ?  [["user_id", 2]]
  Post Load (0.1ms)  SELECT "posts".* FROM "posts" WHERE "posts"."user_id" = ?  [["user_id", 3]]
#=> [["Jack", "post-1, post-2"], ["Adam", "post-3, post-3"], ["John", "post-5, post-6"]]

Now lets try including posts

2.0.0-p645 :023 > User.includes(:posts).map do |user| 
2.0.0-p645 :024 >   [user.name, user.posts.map(&:title).join(', ')] 
2.0.0-p645 :025 > end
  User Load (0.2ms)  SELECT "users".* FROM "users"
  Post Load (0.3ms)  SELECT "posts".* FROM "posts" WHERE "posts"."user_id" IN (1, 2, 3)
  #=> [["Jack", "post-1, post-2"], ["Adam", "post-3, post-3"], ["John", "post-5, post-6"]]

Well it worked. But what if I want to apply published scope on posts.

2.0.0-p645 :026 > User.includes(:posts).map do |user|
2.0.0-p645 :027 >   [user.name, user.posts.published.map(&:title).join(', ')] 
2.0.0-p645 :028 > end
  User Load (0.2ms)  SELECT "users".* FROM "users"
  Post Load (0.3ms)  SELECT "posts".* FROM "posts" WHERE "posts"."user_id" IN (1, 2, 3)
  Post Load (0.1ms)  SELECT "posts".* FROM "posts" WHERE "posts"."user_id" = ? AND "posts"."published" = 't'  [["user_id", 1]]
  Post Load (0.1ms)  SELECT "posts".* FROM "posts" WHERE "posts"."user_id" = ? AND "posts"."published" = 't'  [["user_id", 2]]
  Post Load (0.1ms)  SELECT "posts".* FROM "posts" WHERE "posts"."user_id" = ? AND "posts"."published" = 't'  [["user_id", 3]]
  #=> [["Jack", "post-1, post-2"], ["Adam", "post-3, post-3"], ["John", "post-5, post-6"]]

It fired N + 1 queries. Let's fix this by creating a new association with condition

# app/models/user.rb
class User < ActiveRecord::Base
  has_many :posts
  has_many :published_posts, -> { where('published = ?', true) }, class_name: 'Post'
end

# app/models/post.rb
class Post < ActiveRecord::Base
  belongs_to :user
  scope :published, -> { where(published: true) }
end

Now we will include published_posts instead posts and it will preload posts with published scope applied to them

2.0.0-p645 :029 > User.includes(:published_posts).map do |user| 
2.0.0-p645 :030 >   [user.name, user.published_posts.map(&:title).join(', ')]
2.0.0-p645 :031 > end
  User Load (0.3ms)  SELECT "users".* FROM "users"
  Post Load (0.3ms)  SELECT "posts".* FROM "posts" WHERE (published = 't') AND "posts"."user_id" IN (1, 2, 3)
#=> [["Jack", "post-1, post-2"], ["Adam", "post-3, post-3"], ["John", "post-5, post-6"]]

Voila!

You should use scope instead of condition this way if your scope changes you don't have to change it at two places.

# app/models/user.rb
class User < ActiveRecord::Base
  has_many :posts
  has_many :published_posts, -> { published }, class_name: 'Post'
end

# app/models/post.rb
class Post < ActiveRecord::Base
  belongs_to :user
  scope :published, -> { where(published: true) }
end