如何让 Rails 获得急切的加载计数? [英] How do I get Rails to eager load counts?
问题描述
这与一年前发生变化的问题有关.
我举了一个开箱即用的问题示例，前提是您有可用的 sqlite3:https://github.com/cairo140/rails-eager-loading-counts-demo
I put up an example of the question that should work out of the box, provided you have sqlite3 available: https://github.com/cairo140/rails-eager-loading-counts-demo
git clone git://github.com/cairo140/rails-eager-loading-counts-demo.git
cd rails-eager-loading-counts-demo
rails s
我在存储库中有更完整的文章，但我的一般问题是这个.
I have a fuller write-up in the repository, but my general question is this.
如何使 Rails 预加载计数以最小化数据库查询的方式进行?
每当您在关联上使用 #count 时都会出现 n+1 问题，尽管已通过 #includes(: associated) 在 ActiveRelation 中.一种解决方法是使用 #length，但这只有在调用它的对象已经加载时才有效，更不用说我怀疑它复制了 Rails 内部已经完成的事情.此外，使用 #length 的一个问题是，当关联一开始没有加载而您只需要计数时，它会导致不幸的过载.
The n+1 problem emerges whenever you use #count on an association, despite having included that association via #includes(:associated) in the ActiveRelation. A workaround is to use #length, but this works well only when the object it's being called on has already been loaded up, not to mention that I suspect it duplicates something that the Rails internals have done already. Also, an issue with using #length is that it results in an unfortunate over-loading when the association was not loaded to begin with and the count is all you need.
来自自述文件:
我们可以通过在已经加载的posts 数组(见附录)上运行#length 来避免这个问题，但是如果count 也随时可用也很好.它不仅更加一致；它提供了一个不一定需要加载帖子的访问路径.例如，如果您有一个无论如何都显示计数的部分，但有一半时间，该部分被调用时加载了帖子而一半时间没有，您将面临以下情况:
We can dodge this issue by running #length on the posts array (see appendix), which is already loaded, but it would be nice to have count readily available as well. Not only is it more consistent; it provides a path of access that doesn't necessarily require posts to be loaded. For instance, if you have a partial that displays the count no matter what, but half the time, the partial is called with posts loaded and half the time without, you are faced with the following scenario:
- 使用
#count- n
COUNT条已加载帖子时的样式查询 - n
COUNT个样式查询，当帖子尚未加载时
- Using
#count- n
COUNTstyle queries when posts are already loaded - n
COUNTstyle queries when posts are not already loaded
- 当帖子已经加载时零额外查询
- n
*帖子尚未加载时的样式查询
- Zero additional queries when posts are already loaded
- n
*style queries when posts are not already loaded
在这两种选择之间，没有优势选择.但是最好修改 #count 以推迟到 #length 或访问以其他方式存储在幕后的长度，以便我们可以有以下场景:
Between these two choices, there is no dominant option. But it would be nice to revise #count to defer to #length or access the length that is some other way stored behind the scenes so that we can have the following scenario:
- 使用修改后的
#count- 当帖子已经加载时零额外查询
- n
COUNT个样式查询，当帖子尚未加载时
- Using revised
#count- Zero additional queries when posts are already loaded
- n
COUNTstyle queries when posts are not already loaded
那么这里的正确方法是什么?有没有我忽略的东西(很有可能)?
So what's the correct approach here? Is there something I've overlooked (very, very likely)?
推荐答案
似乎实现这种功能的最佳方式可能是创建 SQL 视图(参考:这里 和 此处)用于您想要的单独模型和子计数对象；及其相关的 ActiveRecord 模型.
It appears that the best way to implement this sort of facility might be to create SQL Views (ref: here and here) for the seperate model-and-child-count objects that you want; and their associated ActiveRecord models.
您也许可以非常聪明，在原始模型上使用子类化并结合
set_table_name :sql_view_name来保留对象上的所有原始方法，甚至可能他们的一些协会.You might be able to be very clever and use subclassing on the original model combined with
set_table_name :sql_view_nameto retain all the original methods on the objects, and maybe even some of their associations.例如，假设我们要在您的示例中添加Post.has_many :comments"，就像上面@Zubin 的回答一样；那么一个人可能能够做到:
For instance, say we were to add 'Post.has_many :comments' to your example, like in @Zubin's answer above; then one might be able to do:
class CreatePostsWithCommentsCountsView < ActiveRecord::Migration def self.up #Create SQL View called posts_with_comments_counts which maps over # select posts.*, count(comments.id) as comments_count from posts # left outer join comments on comments.post_id = posts.id # group by posts.id # (As zubin pointed out above.) #*Except* this is in SQL so perhaps we'll be able to do further # reducing queries against it *as though it were any other table.* end end class PostWithCommentsCount < Post #Here there be cleverness. #The class definition sets up PWCC # with all the regular methods of # Post (pointing to the posts table # due to Rails' STI facility.) set_table_name :posts_with_comment_counts #But then we point it to the # SQL view instead. #If you don't really care about # the methods of Post being in PWCC # then you could just make it a # normal subclass of AR::Base. end PostWithCommentsCount.all(:include => :user) #Obviously, this sort of "upward # looking" include is best used in big lists like "latest posts" rather than # "These posts for this user." But hopefully it illustrates the improved # activerecordiness of this style of solution. PostWithCommentsCount.all(:include => :comments) #And I'm pretty sure you # should be able to do this without issue as well. And it _should_ only be # the two queries.这篇关于如何让 Rails 获得急切的加载计数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
- n
- n
