when use has_many :through in rails, can a query yield fields from all three tables?

Question

In my app, the main objects are Accounts and Phones, with a typical has_many :through Contacts, eg:

Account:
has_many :contacts
has_many :phones, :though => contacts

Phone:
has_many :contacts
has_many :accounts, :though => :contacts

Contact:
belongs_to :account
belongs_to :phone

Contacts has fields signup_status, name There is one Contact per unique Account/Phone pair

For an account with id = 123, which has 5 contacts, each contact having one phone, is there a query that would yield all 5 rows and include all the account fields AND contact fields AND phone fields?

By "Store" do you mean "Account"? – cdesrosiers Sep 26 '12 at 04:25 — cdesrosiers, Sep 26 '12 at 04:25

score 2 · Accepted Answer · answered Sep 26 '12 at 05:15

2

You can use eager loading of associations to get all the data you need in one active record query

@account = Account.includes(:contacts, :phones).find(123)

, which will actually translate into three SQL queries:

SELECT "accounts".* FROM "accounts" WHERE "accounts"."id" = $1 LIMIT 1  [["id", 123]]
SELECT "contacts".* FROM "contacts" WHERE "contacts"."account_id" IN (123)
SELECT "phones".* FROM "phones" WHERE "phones"."id" IN (<phone ids found in prev query>)

All of the records will be loaded into memory and become available through @account. To get the array of contacts and phones, just call @account.contacts and @account.phones, respectively. Note that these calls will not result in re-issued SQL queries, which is the beauty of eager loading.

answered Sep 26 '12 at 05:15

cdesrosiers

8,862
2
28
33

sorry I cannot accept two, becasue it was a toss-up THANK you very much. – jpw Sep 26 '12 at 07:17
just verified the logs showing no addl queries when use some info from phones in view - very cool – jpw Sep 26 '12 at 16:13
right, but i think you would see additional queries if you had used `@account = Account.includes(:contacts => :phone)` instead and called @account.phones. That's because in this case, `@account` doesn't know about the eager loading of phones, only @account.contacts do. – cdesrosiers Sep 26 '12 at 16:43
actually I'm having a lot of trouble in that it returns ONE Account record (for #123) with none of the other fields... so '@account.contacts' or '@account.phones' results in "undefined method contacts" or "undefined method phones" (in my view I put a '@account.inspect' and '@account.contacts.inspect' and '@account.phones.inspect' – jpw Sep 26 '12 at 17:05
never mind, I was using .where() instead of .find() once I used find(0 it worked like a charm! – jpw Sep 26 '12 at 17:30

Wizard of Ogz · Answer 2 · 2012-09-26T05:36:26.993

ActiveRecord isn't quite smart enough to do all that with one SQL query. You can get pretty close, however, by using includes, which will avoid n+1 queries.

Account.includes(:contacts => :phones).where(:id => 123)

ActiveRecord will execute one query to load all Account records, one query to load all Contacts, and one query to load all Phones. See the link below to the documentation for the reason behind this.

if you really wanted to get everything in one SQL query (which can have drawbacks) you should look at ActiveRecord::Associations::Preloader (documentation)

when use has_many :through in rails, can a query yield fields from all three tables?

2 Answers2