在Rails 4.2中,最新的连接和where子句的工作方式似乎没有一种干净的(使用符号...或仅仅不使用字符串SQL)的方法可以在单个查询中连接到同一个表。
例如,我们有一个用户模型,它有一个对家庭地址和工作地址的引用。如果我们想要获取所有生活和工作在印第安纳波利斯的用户,最好的方法是如何构建这个查询呢?
理想情况下,应该像这样:
User.joins(:home_address).join(:work_address).
where(home_address: { city: 'Indianapolis' } ).
where(work_address: { city: 'Indianapolis' } ).count
然而,User模型上的home_address和work_address(它们是belongs_to关联)对where子句没有任何影响。您有两个用户地址,显然这几乎足够了(因为我在该表上查询两次)。
这里有一个可以工作的解决方案,但并不像上面的解决方案那样简洁。
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
所以问题的要点是,这是有意为之的吗(在Rails的早期版本中,可以在where子句中引用belongs_to关联名称)?如果是这样,是否有一种技术可以清理掉它而不使用字符串SQL?
明确一点,在Rails的早期版本中,belongs_to关联适用于此类型的查询:这曾经有效...但在Rails 4.2中它不再起作用:
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>
然而在最新版本的Rails中,它不再起作用:
[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)>
请注意 .to_sql 的差异...一个使用由 belongs_to 关联定义的别名,而新的则使用我们在 where 哈希中放置的任何内容。
User.joins(:home_address, :work_address)
而不是User.joins(:home_address).join(:work_address)
吗? - Arup Rakshit