ruby-on-rails - 左连接和 .includes()

Question

据我了解，在 Rails 中执行 LEFT JOIN 的唯一方法是手动编写 SQL，如下所示:

Company.joins('LEFT JOIN people ON companies.id = people.company_id')

但是，加入其他表并同时使用 .includes(:people) 变得非常棘手。

我需要使用 .includes(:people) 否则 Rails 会在我访问 people 方法时生成额外的数据库请求(相关问题:When joining table, rails anyway makes additional request when accessing fields from joined table)

所以，这行得通:

Company.joins('LEFT JOIN people ON companies.id = people.company_id')

这行得通:

Company.joins('LEFT JOIN people ON companies.id = people.company_id')
      .includes(:people)

这行得通:

Company.joins('LEFT JOIN people ON companies.id = people.company_id')
      .includes(:people).joins(:city)

这不起作用:

Company.joins('LEFT JOIN people ON companies.id = people.company_id')
      .includes(:people).joins(:city).includes(:city)

它最终加入 people 两次，当然 db 提示列名不明确:people.id。

生成的SQL是:

SELECT "companies"."id" AS t0_r0, "companies"."title" AS t0_r1, 
"companies"."address" AS t0_r2, "companies"."city_id" AS t0_r3, 
"companies"."created_at" AS t0_r4, "companies"."updated_at" AS t0_r5, 
"people"."id" AS t1_r0, "people"."name" AS t1_r1, "people"."surname" AS t1_r2,
"people"."patronymic" AS t1_r3, "people"."company_id" AS t1_r4,
"people"."created_at" AS t1_r5, "people"."updated_at" AS t1_r6,
"cities"."id" AS t2_r0, "cities"."title" AS t2_r1, "cities"."created_at" AS t2_r2,
"cities"."updated_at" AS t2_r3, "cities"."test" AS t2_r4 FROM "companies" 
INNER JOIN "cities" ON "cities"."id" = "companies"."city_id"
LEFT OUTER JOIN "people" ON "people"."company_id" = "companies"."id"
LEFT JOIN people ON companies.id = people.company_id

我的理解是，当我们手动编写 JOIN SQL 代码时，Rails 无法控制它，它无法确定 people 表是否已经加入。

如果我这样写，它可以工作:

Company.joins(:people).includes(:people).joins(:city).includes(:city)

然后，它使用 INNER JOIN，但我需要 LEFT JOIN。我仍然找不到使用 LEFT JOIN 的方法，以便它在 Rails 的控制之下。

怎么做？

Answer 1

知道了:我们需要将 .references() 与 .includes 结合使用；然后，Rails 执行 LEFT JOIN，我们可以在 SQL 查询中引用连接表。

所以，这行得通:

Company.includes(:people).references(:people).includes(:city).references(:city)

嗯，这看起来很乱。