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

flowerwrong · 发布于 2015年11月21日 · 最后由 mr_y 回复于 2015年11月21日 · 942 次阅读
9442

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
......
......
共收到 1 条回复
20635

可以试一下

Cat.includes(articles: :article_comments]).order("articles.view_counts desc")
需要 登录 后方可回复, 如果你还没有账号请点击这里 注册