多个外键关联的表之间怎么查询啊？

0 0

多个外键关联的表之间怎么查询啊？3

关联如下：

class Contract < ActiveRecord::Base
  belongs_to :buyer, :class_name => 'Customer', :foreign_key => 'buyer_customer_id'#客户出面购买者比如采购部之类
  belongs_to :user, :class_name => 'Customer', :foreign_key => 'user_customer_id'#客户实际使用者比如研发部之类
end
 
class Customer < ActiveRecord::Base
  has_many :as_buyer_in_contracts, :class_name => 'Contract', :foreign_key => 'buyer_customer_id'
  has_many :as_user_in_contracts, :class_name => 'Contract',:foreign_key => 'user_customer_id'
end

现在我想查询比如和所有姓王的客户签的合同，就应该是这么写：

Contract.where("customers.name like '王%'").includes(:buyer,:user)

但事实上这样只查得到buyer里姓王的而查不到user里姓王的，怎么能两个关联都查上呢……

问题补充：

cxh116 写道

有个join
http://guides.rubyonrails.org/active_record_querying.html#joining-tables

引用

Contract.join(:user).where("users.name like '王%'").includes(:buyer,:user)

我这里没有用到连接表的多对多关联关系，也要用joins？(你说的是joins吧？)

问题补充：

cxh116 写道

嗯方法名是jions

Contract.joins("INNER JOIN customers AS c1 ON c1.id = contracts.buyer_customer_id INNER JOIN customers AS c2 ON c2.id = contracts.user_customer_id ").where("c1.name like '王%' or c2.name like '王%'")

includes估计你要看一下文档,不知道这样可以不,不行的话,你把表别名c1和c2改成buyer,user

单查这两个字段的话我试了下，的确可以。
但是……我貌似还需要includes别的字段，比如：
Contract.joins(:user).where("currencies.name = ?", "USD").includes([:currency, :user, :buyer])
这样的时候，它就会报错“Mysql2::Error: Unknown column 'users_contracts.id' in 'field list': ”，也就是说它会试图把users和contracts认成一个新表？

问题补充：

cxh116 写道

测试了一下,没办法在joins又includes
看看这文章http://stackoverflow.com/questions/1208636/rails-include-vs-joins
英文不是很好

class Customer < ActiveRecord::Base
  has_many :c1s, :class_name => 'Contract', :foreign_key => 'c1_id'
  has_many :c2s, :class_name => 'Contract', :foreign_key => 'c2_id'
end

class Contract < ActiveRecord::Base
  belongs_to :c1, :class_name => 'Customer', :foreign_key => 'c1_id'
  belongs_to :c2, :class_name => 'Customer', :foreign_key => 'c2_id'
end

我在rails console测试

引用

ree-1.8.7-2011.03 :042 > Contract.includes(:c1,:c2)
Contract Load (0.2ms) SELECT `contracts`.* FROM `contracts`
Customer Load (0.1ms) SELECT `customers`.* FROM `customers` WHERE `customers`.`id` IN (1)
Customer Load (0.1ms) SELECT `customers`.* FROM `customers` WHERE `customers`.`id` IN (1)
=> [#<Contract id: 1, c1_id: 1, c2_id: 1, created_at: "2011-10-18 12:59:02", updated_at: "2011-10-18 12:59:02">]

ree-1.8.7-2011.03 :043 > Contract.joins(:c1, :c2)
Contract Load (0.6ms) SELECT `contracts`.* FROM `contracts` INNER JOIN `customers` ON `customers`.`id` = `contracts`.`c1_id` INNER JOIN `customers` `c2s_contracts` ON `c2s_contracts`.`id` = `contracts`.`c2_id`
=> [#<Contract id: 1, c1_id: 1, c2_id: 1, created_at: "2011-10-18 12:59:02", updated_at: "2011-10-18 12:59:02">]

可以看出,includes生成了三条查询语句,而且还用到in查询,而joins只用了一条查询语句,之后要拿c1或c2是根据主键查询
主键查询数据库是有优化的,而且rails支持根据主键直接把对象缓存到内存

之前我有别的表用到过既includes又joins的，但对同一个表有两个外键的貌似还真没办法了，在Rails里一条条写SQL语句应该是可以，但我白天看了一下它报错的SQL语句，在Notepad++里占了一整页……自己写的话

Rails

2011年10月18日 11:22

hexawing
4
0 0 6

3个答案按时间排序按投票排序

0 0

测试了一下,没办法在joins又includes
看看这文章http://stackoverflow.com/questions/1208636/rails-include-vs-joins
英文不是很好

class Customer < ActiveRecord::Base
  has_many :c1s, :class_name => 'Contract', :foreign_key => 'c1_id'
  has_many :c2s, :class_name => 'Contract', :foreign_key => 'c2_id'
end

class Contract < ActiveRecord::Base
  belongs_to :c1, :class_name => 'Customer', :foreign_key => 'c1_id'
  belongs_to :c2, :class_name => 'Customer', :foreign_key => 'c2_id'
end

我在rails console测试

引用