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.
3 Answers
Answers 1
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.
Answers 2
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.
Answers 3
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.
0 comments:
Post a Comment