在急于加载活动记录关联时避免双重查询

Question

为了进行单个DB查询，我渴望加载Posts及其翻译数据(使用Rails6和Mobility(*((，但它却产生了两个SQL查询：

# app/models/post.rb
class Post < ApplicationRecord
extend Mobility
translates :title, :description, backend: :table
end
# app/controllers/posts_controller.rb
class PostsController < ApplicationRecord
def index
@posts = Post.eager_load(:translations).limit(3)
end
end

<%# app/views/posts/index.html.erb %>
<% @posts.each do |post| %>
<h1><%= post.title %></h1>
<div><%= post.description %></div>
<% end %>

结果：

• 首先，选择所有Post ID
• 然后使用WHERE IN返回具有这些ID的帖子的所有属性

SELECT DISTINCT "posts"."id" FROM "posts"
LEFT OUTER JOIN "post_translations" 
ON "post_translations"."post_id" = "posts"."id" LIMIT $1  [["LIMIT", 3]]
SELECT "posts"."id" AS t0_r0, "posts"."created_at" AS t0_r1, "posts"."updated_at" AS t0_r2, "post_translations"."id" AS t1_r0, "post_translations"."locale" AS t1_r1, "post_translations"."created_at" AS t1_r2, "post_translations"."updated_at" AS t1_r3, "post_translations"."title" AS t1_r4, "post_translations"."description" AS t1_r5, "post_translations"."post_id" AS t1_r6 FROM "posts" 
LEFT OUTER JOIN "post_translations" 
ON "post_translations"."post_id" = "posts"."id" 
WHERE "posts"."id" IN ($1, $2, $3)  [["id", "00060a7d-b846-5fc5-a372-1fc3462c695c"], ["id", "008db504-6fb4-5e90-bdca-4293ebe6d920"], ["id", "034944c1-4067-5ae5-89aa-4777ef14d66b"]]

如何避免这种ID在内存中的双SQL语句

---

(*(关于移动的一个注意事项

Mobility文档中有生成单个SQL语句的示例，但正如Chris Salzberg所指出的，在本示例中根本没有使用其查询API，因此不应成为罪魁祸首。为了证明这个问题可能与移动性无关，而是与活动记录本身有关，下面是一个从移动性中剥离出来的等效代码，它显示了相同的双重查询问题(注意：这只是为了演示目的，因为我确实想继续使用移动性(：

class Post < ApplicationRecord
has_many :translations, ->{ where(locale: I18n.locale) }
%i(title description).each do |attr|
define_method(attr) do
translations.first.send(attr)
end
end
class Translation < ApplicationRecord; end
end

<%# app/views/posts/index.html.erb %>
<% Post.eager_load(:translations).limit(3).each do |post| %>
<h1><%= post.title %></h1>
<div><%= post.description %></div>
<% end %>

Answer 1

如果您试图从集合中获取一些非常特定的属性，那么CollectionProxy将为您提供一个查询。如果没有提供任何属性(列(，则在执行OUTER JOIN查询之前，它将执行一个不同的查询。

老实说，我还没有通读整个实现来证实它背后的原因

但让我给你看一件事。

2.5.1 :037 > Post.eager_load(:translations).each do |post|
2.5.1 :038 >     puts post.title
2.5.1 :039?>   end

SQL (0.5ms)  SELECT "posts"."id" AS t0_r0, "posts"."title" AS t0_r1, "posts"."created_at" AS t0_r2, "posts"."updated_at" AS t0_r3, "post_translations"."id" AS t1_r0, "post_translations"."title" AS t1_r1, "post_translations"."content" AS t1_r2, "post_translations"."locale" AS t1_r3, "post_translations"."post_id" AS t1_r4, "post_translations"."created_at" AS t1_r5, "post_translations"."updated_at" AS t1_r6 FROM "posts" LEFT OUTER JOIN "post_translations" ON "post_translations"."post_id" = "posts"."id"
title 1
title 2
title 3
title 4

在上面的例子中，您可以看到eager_load做了您期望的事情。在类似的情况下，您没有提到所需的属性，我认为当它懒惰求值时，除了OUTER JOIN查询之外，它还会选择distinct查询

2.5.1 :040 > Post.eager_load(:translations)
SQL (0.3ms)  SELECT  DISTINCT "posts"."id" FROM "posts" LEFT OUTER JOIN "post_translations" ON "post_translations"."post_id" = "posts"."id" LIMIT ?  [["LIMIT", 11]]
SQL (0.5ms)  SELECT "posts"."id" AS t0_r0, "posts"."title" AS t0_r1, "posts"."created_at" AS t0_r2, "posts"."updated_at" AS t0_r3, "post_translations"."id" AS t1_r0, "post_translations"."title" AS t1_r1, "post_translations"."content" AS t1_r2, "post_translations"."locale" AS t1_r3, "post_translations"."post_id" AS t1_r4, "post_translations"."created_at" AS t1_r5, "post_translations"."updated_at" AS t1_r6 FROM "posts" LEFT OUTER JOIN "post_translations" ON "post_translations"."post_id" = "posts"."id" WHERE "posts"."id" IN (?, ?, ?, ?)  [["id", 1], ["id", 2], ["id", 3], ["id", 4]]
=> #<ActiveRecord::Relation [#<Post id: 1, title: nil, created_at: "2021-07-08 12:42:13", updated_at: "2021-07-09 15:32:48">, #<Post id: 2, title: nil, created_at: "2021-07-09 15:33:50", updated_at: "2021-07-09 15:33:50">, #<Post id: 3, title: nil, created_at: "2021-07-09 15:33:55", updated_at: "2021-07-09 15:33:55">, #<Post id: 4, title: nil, created_at: "2021-07-09 15:33:57", updated_at: "2021-07-09 15:33:57">]>

希望这在某种程度上有所帮助。如果有任何评论，请发表，这样我可以澄清是否可以。：(