Left join and .includes()

Question

As far as I understood, the only way in Rails to perform a LEFT JOIN is to write SQL manually, like that:

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

But then, it becomes very tricky to join other tables and use .includes(:people) as well.

I need to use .includes(:people) because otherwise Rails generates additional db request whenever I access people method (related question: When joining table, rails anyway makes additional request when accessing fields from joined table)

So, this works:

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

This works:

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

This works:

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

This does not work:

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

It ends up joining people twice, and of course db complains about ambiguous column name: people.id.

The generated SQL is:

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

My understanding is that when we write JOIN SQL code manually, Rails has no control of it and it can't figure out that people table is already joined.

If I write it like this, it works:

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

But then, it uses INNER JOIN, but I need for LEFT JOIN. I still can't find the way to use LEFT JOIN so that this is under control of Rails.

How to do that?

Answer 1

Got it: we need to use .references() in conjunction with .includes; then, Rails performs LEFT JOIN and we can reference joined table in SQL query.

So, this works:

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

Well, this seems messy area.