CODEWITHSUNDEEP
ruby-on-railsleft-join
Dmitry Frank
Dmitry Frank

Reputation: 10767

Left join and .includes()

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?

Upvotes: 3

Views: 5570

Answers (2)

Aiman Saeed
Aiman Saeed

Reputation: 1

I recommend Company.joins(:people, :city).select("*"). Assuming Company has direct association to People and City both.

This is even possible with indirect relations. For instance Company "has_many" People and City. Then, City "has_many" Views. Company.joins(:people, city: :views).select("*")

If there are two nested relations to inner join with. Lets add one more relation with City. City "has_many" Views & Whales.

Company.joins(:people, city: [:views, :whales]).select("*")

Upvotes: 0

Dmitry Frank
Dmitry Frank

Reputation: 10767

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.

Upvotes: 10

Related Questions