Rails Association 中 SQL Order or Where 导致的 N+1 query 问题

flowerwrong · November 21, 2015 · Last by mr_y replied at November 21, 2015 · 2397 hits

N + 1 query

models
class Cat < ActiveRecord::Base
  has_many :articles
end

class Article < ActiveRecord::Base
  default_scope { where(is_publish: 1) }
  scope :view_counts_order, -> { order(view_counts: :desc) }

  belongs_to :cat
  has_many :comments
end

class Comment < ActiveRecord::Base
  belongs_to :article # , counter_cache: :article_comments_count
end
controller
# 问题控制器
Cat.includes(articles: :article_comments]).all.each do |cat|
  cat.articles.view_counts_order.each do |article|
    p article.comments.size
  end
end

# 不加order没 N + 1 query问题
Cat.includes(articles: :article_comments]).all.each do |cat|
  cat.articles.each do |article|
    p article.comments.size
  end
end

问题

如何减少 cat.articles.view_counts_order 产生的多余 sql?同理,如果 articles 后面加 where 查询也会导致多余 sql。

多余 sql

  Article Load (0.1ms)  SELECT `articles`.* FROM `articles` WHERE `articles`.`is_publish` = 1 AND `articles`.`cat_id` = 6  ORDER BY `articles`.`view_counts` DESC
   (0.1ms)  SELECT COUNT(*) FROM `comments` WHERE `comments`.`article_id` = 200
0
   (0.1ms)  SELECT COUNT(*) FROM `comments` WHERE `comments`.`article_id` = 191
0
   (0.1ms)  SELECT COUNT(*) FROM `comments` WHERE `comments`.`article_id` = 174
0

  Article Load (0.1ms)  SELECT `articles`.* FROM `articles` WHERE `articles`.`is_publish` = 1 AND `articles`.`cat_id` = 7  ORDER BY `articles`.`view_counts` DESC
   (0.1ms)  SELECT COUNT(*) FROM `comments` WHERE `comments`.`article_id` = 188
0
   (0.1ms)  SELECT COUNT(*) FROM `comments` WHERE `comments`.`article_id` = 185
0
   (0.1ms)  SELECT COUNT(*) FROM `comments` WHERE `comments`.`article_id` = 183
0
......
......

可以试一下

Cat.includes(articles: :article_comments]).order("articles.view_counts desc")
You need to Sign in before reply, if you don't have an account, please Sign up first.