Multiple belongs_to joins to the same table (In Rails 4.2 did the ability to do this in a clean way change?)

Question

With the latest way that joins and the where clauses work in Rails 4.2, it appears there is not a clean (using symbols...or just not using string SQL) way to join to the same table from a single query.

For instance, we have a user model, and it has a reference to a home address, and a work address. If we want to get all of the users that live and work in Indianapolis, what is the best way to go about building this query?

The ideal option would be something like this:

User.joins(:home_address).join(:work_address).
  where(home_address: { city: 'Indianapolis' } ).
  where(work_address: { city: 'Indianapolis' } ).count

However, the home_address and work_address (which are belongs_to associations on the User model) don't have any effect on the where clause. You have two user addresses, and obviously that is nearly good enough (since I am querying on that table twice).

Here is something that works, but just isn't as clean as the solution from above:

User.joins('INNER JOIN addresses as home_address ON home_address_id = home_address.id').
  joins('INNER JOIN addresses as work_address ON work_address_id = work_address.id').
  where(home_address: { city: 'Indianapolis' } ).
  where(work_address: { city: 'Indianapolis' } ).count

So the point of the question is, was this intentional (in previous versions of Rails, the belongs_to association name could be referenced in the where clause)? And, if it was, is there a technique that I'm not seeing of how to clean this up without using string SQL?

To be clear, in an earlier version of Rails, the belongs_to association worked for this type of query: That used to work...in Rails 4.2 it no longer works:

irb(main):001:0> User.joins(:home_address, :work_address).where(home_address: { city: 'Indianapolis' }, work_address: { city: 'Indianapolis' }).to_sql
"SELECT \"users\".* FROM \"users\" INNER JOIN \"addresses\" ON \"addresses\".\"id\" = \"users\".\"home_address_id\" INNER JOIN \"addresses\" \"work_addresses_users\" ON \"work_addresses_users\".\"id\" = \"users\".\"work_address_id\" WHERE ((\"addresses\".\"city\" = 'Indianapolis' AND \"work_addresses_users\".\"city\" = 'Indianapolis'))"
irb(main):002:0> Rails::VERSION::STRING
"4.0.5"
irb(main):003:0>

However in the latest version of Rails, it no longer works:

[1] pry(main)> User.joins(:home_address, :work_address).
[1] pry(main)* where(home_address: { city: 'Indianapolis' }, work_address: { city: 'Indianapolis' }).count  
ActiveRecord::StatementInvalid: PG::UndefinedTable: ERROR:  missing FROM-clause entry for table "home_address"
LINE 1: ...ses_users"."id" = "users"."work_address_id" WHERE "home_addr...
                                                             ^
: SELECT COUNT(*) FROM "users" INNER JOIN "addresses" ON "addresses"."id" = "users"."home_address_id" INNER JOIN "addresses" "work_addresses_users" ON "work_addresses_users"."id" = "users"."work_address_id" WHERE "home_address"."city" = $1 AND "work_address"."city" = $2
from /Users/ar3/.rvm/gems/ruby-2.2.1/gems/activerecord-4.2.1/lib/active_record/connection_adapters/postgresql_adapter.rb:637:in `prepare'
[2] pry(main)> Rails::VERSION::STRING
"4.2.1"
[3] pry(main)> User.last.home_address.present?
true
[4] pry(main)> User.last.work_address.present?
true
[5] pry(main)> User.joins(:home_address, :work_address).where(home_address: { city: 'Indianapolis' }, work_address: { city: 'Indianapolis' }).to_sql
"SELECT \"users\".* FROM \"users\" INNER JOIN \"addresses\" ON \"addresses\".\"id\" = \"users\".\"home_address_id\" INNER JOIN \"addresses\" \"work_addresses_users\" ON \"work_addresses_users\".\"id\" = \"users\".\"work_address_id\" WHERE \"home_address\".\"city\" = 'Indianapolis' AND \"work_address\".\"city\" = 'Indianapolis'"
[6] pry(main)>

Notice the difference in the .to_sql...one uses the alias defined by the belongs_to association, the new one, uses whatever we put in the where hash.

I think this may be helpful!! http://stackoverflow.com/a/14116273/2397717 — Sookie Singh, Jun 08 '15 at 15:05
can you try `User.joins(:home_address, :work_address)` instead of `User.joins(:home_address).join(:work_address)` ? — Arup Rakshit, Jun 08 '15 at 15:12

score 2 · Answer 1 · answered Jun 08 '15 at 16:06

2

where clauses in ActiveRecord always use table names. As long as you are querying for the same thing on both associations you can do:

User.joins(:home_address, :work_address)
  .where(addresses: { city: 'Indianapolis' }).count

addresses being your table which contains the addresses.

But if you need a different where clause for both home_address and work_address then as far as I know you're out of luck. Someone correct me if I'm wrong, I couldn't find a way.

This is a great example where you can use STI though to enable you to do this an improve your architecture.

answered Jun 08 '15 at 16:06

with `Hash` style inside the `where`.. you have can use the `association` names.. not really needed table names.. – Arup Rakshit Jun 08 '15 at 18:05
2

@ArupRakshit did you try that on rails4? I replicated the problem in question and could not get your suggestion to work using the association in the where clause with the hash syntax. – Jun 09 '15 at 05:36
I'll try today. I was away from my computer.. something changed it seems as OP told me on my answer and shown in the post itself. – Arup Rakshit Jun 09 '15 at 06:06

Multiple belongs_to joins to the same table (In Rails 4.2 did the ability to do this in a clean way change?)

1 Answers1