sql - Rails：如何在自定义关联列时在单个查询中加载关联

Question

如果我使用eager_load在单个查询中获取我想要的关联，我会得到太多列：

scope = Product.eager_load(:account).to_a
scope.last.account.name

然后我得到一个查询，如下所示：

 SQL (3.3ms)  SELECT "products"."id" AS t0_r0, "products"."account_id" AS t0_r1, "products"."notes" AS t0_r2, "products"."created_at" AS t0_r3, "products"."updated_at" AS t0_r4, "products"."rep_id" AS t0_r5, "products"."senior_rep_id" AS t0_r6, "products"."name_id" AS t0_r7...

我的目标是获得我想要的列，例如我可能期望通过调用.select("intakes.id, intakes.account_id, accounts.id, accounts.name"). 但是，如果我将其添加到我的查询中，它只是将该行添加到前面并基本上忽略它：

 SQL (3.3ms)  SELECT products.id, products.account_id, accounts.id, accounts.name, "products"."id" AS t0_r0, "products"."account_id" AS t0_r1, "products"."notes" AS t0_r2, "products"."created_at" AS t0_r3, "products"."updated_at" AS t0_r4, "products"."rep_id" AS t0_r5, "products"."senior_rep_id" AS t0_r6, "products"."name_id" AS t0_r7...

所以现在我的查询更长了，但我没有得到任何好处。我也可以尝试left_joins：

scope = Product.left_joins(:account).select("products.id, products.account_id, accounts.id, accounts.name").to_a

这将产生 N+1 个查询，其中忽略关联的选择：

Products Load (0.9ms)  SELECT products.id, products.account_id, accounts.id, accounts.name FROM "products" LEFT OUTER JOIN "accounts" ON "accounts"."id" = "products"."account_id"
Account Load (0.2ms)  SELECT "accounts".* FROM "accounts" WHERE "accounts"."id" = $1 LIMIT $2  [["id", 16], ["LIMIT", 1]]

但是，如果我识别出选定的列 DID 去某个地方，我可以避免 N+1，在那个accounts.name映射到name产品上：

scope.last.attributes["name"]

从技术上讲，这在单个查询中获得了我想要的信息，但是当构建具有许多关联的真实查询时，尝试重命名并将此数据重新映射为许多自定义名称突然让我想知道为什么我首先使用 ActiveModel。是否有更多的“Rails Way”来做到这一点，scope.last.account.name它的价值仍然会以我使用的方式设置eager_load？

标签： sqlruby-on-railseager-loading

Answer 1

您可以留下查询scope = Product.left_joins(:account).select("products.id, products.account_id, accounts.id, accounts.name").to_a，而不是您的第二行应该scope.last.name阻止第二个数据库请求。如果您在 account 和 product 表中有相同的列，则在 select 方法中为它们设置不同的名称（即scope = Product.left_joins(:account).select("products.id, products.account_id, accounts.id, accounts.name as account_name").to_a，而不是像scope.last.account_name.