Rails 4 Eager负载限制子查询 [英] Rails 4 Eager load limit subquery

问题描述

有没有一种方法可以避免在急于加载时又将n + 1应用于子查询的问题？
我想避免很多这样的sql查询：

  Category.all.each做| category | 
 category.posts.limit（10）
结尾
  

但我也想要每个类别只能获得10个帖子，因此获取所有帖子的标准渴望加载是不够的。

 类别。 include（：posts）.all 
  

解决此问题的最佳方法是什么？ N + 1是限制每个类别的帖子数量的唯一方法吗？

解决方案

来自跟踪文档

如果您希望使用指定的：limit选项加载关联，它将被忽略，并返回所有关联的对象

因此，给出以下模型定义

  class类别< ActiveRecord :: Base 
 has_many：posts 
 has_many：included_posts，-> {limit 10}，class_name：帖子 
结束
  

呼叫 Category.find（1）.included_posts 将按预期工作，并在查询中应用限制10。但是，如果尝试执行 Category.includes（：included_posts）.all ，则 limit 选项将被忽略。如果查看急切加载生成的SQL，就会看到为什么会这样

  Category.includes（：posts） .all 
 
类别加载（0.2毫秒）选择类别。*从类别 
帖子加载（0.4毫秒）选择帖子。*从帖子中选择帖子 。 category_id IN（1、2、3）
  

如果添加了 LIMIT 子句查询，它将按您的期望返回每个类别的10个职位的 total 和 not 10个职位。 p>

回到您的问题，我会急于加载所有帖子，然后使用 first（10）

  categories = Category.includes（：posts）.all 
 Categories.first.posts.first（10）
  

尽管您正在向内存中加载更多模型，但是由于您只是在做，这势必会提高性能。与n + 1相比，对数据库进行了2次调用。干杯。

Is there a way to avoid the n+1 problem when eager loading and also applying a limit to the subquery? I want to avoid lots of sql queries like this:

Category.all.each do |category|
  category.posts.limit(10)
end

But I also want to only get 10 posts per category, so the standard eager loading, which gets all the posts, does not suffice:

Category.includes(:posts).all

What is the best way to solve this problem? Is N+1 the only way to limit the amount of posts per category?

解决方案

From the Rails docs

If you eager load an association with a specified :limit option, it will be ignored, returning all the associated objects

So given the following model definition

class Category < ActiveRecord::Base
  has_many :posts
  has_many :included_posts, -> { limit 10 }, class_name: "Post"
end

Calling Category.find(1).included_posts would work as expected and apply the limit of 10 in the query. However, if you try to do Category.includes(:included_posts).all the limit option will be ignored. You can see why this is the case if you look at the SQL generated by an eager load

Category.includes(:posts).all

Category Load (0.2ms)  SELECT "categories".* FROM "categories"
Post Load (0.4ms)  SELECT "posts".* FROM "posts" WHERE "posts"."category_id" IN (1, 2, 3)

If you added the LIMIT clause to the posts query, it would return a total of 10 posts and not 10 posts per category as you might expect.

Getting back to your problem, I would eager load all posts and then limit the loaded collection using first(10)

categories = Category.includes(:posts).all
categories.first.posts.first(10)

Although you're loading more models into memory, this is bound to be more performant since you're only making 2 calls against the database vs. n+1. Cheers.

这篇关于Rails 4 Eager负载限制子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！