sql - 如何在 Rails 4 中跨多个表构建高效的选择命令？

Question

试图找出一种有效的方法来选择具有跨多个表的属性的记录。这是基本设置:

structure

• 植物(字段:id、name_id、location_id、颜色)(1000 条记录)
• 姓名(字段:id、Common_name)(50 条记录)
• 位置(字段:id、Bed_name)(125 条记录)

model

• 植物 - belongs_to 名称，belongs_to 位置
• 名称 - has_many 植物
• 位置 - has_many 植物

我的目标是输出侧院中每朵玫瑰的列表，并显示颜色，但我卡在了选择命令上。如果我得到所有植物 ( p = Plant.all )，我知道我可以使用类似 <%= "#{p.name.common_name} in bed #{p.location.bed_name} has a color of #{p.color}" %> 的语句轻松创建我的输出如果我进行两次连接，我会查看更多我需要的记录和更长的搜索时间。举个例子 - 我在 16 个不同的花坛上有 67 朵玫瑰，但是，我在旁边的院子里只有 3 朵玫瑰。

我的直觉告诉我，我应该能够做类似的事情:选择名称为 Rose 的所有植物，然后从该选择中选择侧院中的所有 Roses。

谁能帮我指出正确的方向？

Answer 1

您可以像这样将它们组合成一个查询:

Plant.joins(:name, :location).where(names: { common_name: "rose" }, locations: { bed_name: "side" })

这会导致像这样的单个 SQL 查询:

SELECT "plants".* FROM "plants" INNER JOIN "names" ON "names"."id" = "plants"."name_id" INNER JOIN "locations" ON "locations"."id" = "plants"."location_id" WHERE "names"."common_name" = 'rose' AND "locations"."bed_name" = 'side'

请注意，您必须在 where 子句中使用复数 table names，但在 joins 中使用单数 association name 子句。

假设您的表已正确编入索引，即使是巨大的表，这也将几乎立即运行。

这是一个简单的示例，但您可以使用条件进行相当复杂的连接。完整的详细信息可以在 ActiveRecord documentation 中找到.

编辑

根据@Dan 的评论，您可以通过使用 includes 预取连接中的关联数据来加快速度:

Plant.includes(:name, :location).where(names: { common_name: "rose" }, locations: { bed_name: "side" })

这将同时从names 和locations 加载相关记录。 includes 可以方便地消除(或至少减少)N+1 查询。它还足够聪明，知道何时可以在单个查询中检索所有数据，并在更有意义时回退到多个查询；您不必考虑它(尽管有时它会降低效率，因此如果您认为它会降低性能，请留意您的日志)。

在这种情况下使用 includes 非常有效，导致包含关联数据的单个 SQL 查询:

 SELECT "plants"."id" AS t0_r0, "plants"."color" AS t0_r1, "plants"."name_id" AS t0_r2, "plants"."location_id" AS t0_r3, "plants"."created_at" AS t0_r4, "plants"."updated_at" AS t0_r5, "names"."id" AS t1_r0, "names"."common_name" AS t1_r1, "names"."created_at" AS t1_r2, "names"."updated_at" AS t1_r3, "locations"."id" AS t2_r0, "locations"."bed_name" AS t2_r1, "locations"."created_at" AS t2_r2, "locations"."updated_at" AS t2_r3 FROM "plants" LEFT OUTER JOIN "names" ON "names"."id" = "plants"."name_id" LEFT OUTER JOIN "locations" ON "locations"."id" = "plants"."location_id" WHERE "names"."common_name" = 'rose' AND "locations"."bed_name" = 'side'