Rails: eager-loading on an already-left-joined table?

Question

We are already left-joining a table so that we can order by a column, if the relation exists:

people = Person
  .joins("LEFT JOIN addresses ON addresses.id = people.address_id")
  .order("addresses.country")
  .all

This results in a single SQL query, but I'd like to have people.first.address not trigger SQL to load the address. I am left-joining because some people don't have addresses.

.includes(:address) triggers a separate query.

You can do what I'm suggesting with inner joins, using includes, but that triggers 2 SQL queries:

Person.includes(:address).all

While joins + includes triggers only one (but INNER joins):

Person.joins(:address).includes(:address).all

Active record also uses left joins if you force a join while eager-loading .eager_load(:addresses).

Can you take an existing left-join and have rails eager-load with those results? So far I can't find this.

Answer 1

Try this:

people = Person.
  eager_load(:address).
  merge(Address.order("coalesce(country, '')")).
  all

people.first.address

eager_load forces eager loading by performing a LEFT OUTER JOIN.

I added a coalesce on country so you have more control over where people without addresses appear in the results.

Here is what it looks like for me:

people = Person.
  eager_load(:address).
  merge(Address.order("coalesce(country, '')")).
  all

(0.5ms)  SELECT DISTINCT COUNT(DISTINCT "people"."id") FROM "people" LEFT OUTER JOIN "addresses" ON "addresses"."person_id" = "people"."id"
SQL (2.1ms)  SELECT "people"."id" AS t0_r0, "people"."name" AS t0_r1, "people"."created_at" AS t0_r2, "people"."updated_at" AS t0_r3, "addresses"."id" AS t1_r0, "addresses"."person_id" AS t1_r1, "addresses"."address" AS t1_r2, "addresses"."country" AS t1_r3, "addresses"."created_at" AS t1_r4, "addresses"."updated_at" AS t1_r5 FROM "people" LEFT OUTER JOIN "addresses" ON "addresses"."person_id" = "people"."id" ORDER BY coalesce(addresses.country, '')


people.first.address
nil

people.last.address
#<Address:0x007febabb508a8> {
            :id => 1,
     :person_id => 4,
       :address => "24175 Gerhold Prairie",
       :country => "O",
    :created_at => Thu, 01 Feb 2018 18:47:45 UTC +00:00,
    :updated_at => Thu, 01 Feb 2018 18:47:45 UTC +00:00
}

Note that no queries are run when you access the addresses

I'll point that you've ordered by a table that left outer joined, so you need to decide how you want to treat nulls.

Answer 2

You can uses #references with #includes to LEFT JOIN your relation and achieve what you are after.

people = Person
  .includes(:addresses)
  .references(:addresses)
  .order("addresses.country")

This will yield one query and all the people, whether or not they have an address, and also eager load the addresses to avoid the dreaded N + 1 queries.

The documentation doesn't do a great job of explaining that #references will add a LEFT JOIN but it's purpose is to allow adding SQL clauses (WHERE, ORDER, GROUP, etc.) on the relation in conjunction with #includes.

Answer 3

Well, in your case you could select your query creating an alias for the fields you are going to use from address something like this:

people = Person
         .joins("LEFT JOIN addresses ON addresses.id = people.address_id")
         .select("people.*, addresses.country as address_country")
         .order("address_country")
         .all

This won't change your desired query and will not result in extra queries, not for country at least.