rails/rails — Issue #3360: How should I write the ActiveRecord when there are multiple associations?
Author: hexawing
Created: 2011-10-18 14:03:58+00:00
State: open

The models are like this:

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'
belongs_to :currency
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

class Currency < ActiveRecord::Base
has_many :contracts
end

And below is the data:

Contract
+----+-------------------+------------------+-------------+
| id | buyer_customer_id | user_customer_id | currency_id |
+----+-------------------+------------------+-------------+
| 1 | 1 | 3 | 3 |
| 2 | 2 | 2 | 2 |
| 3 | 2 | 1 | 2 |
| | | | |

Customer
+----+-------------------+
| id | name |
+----+-------------------+
| 1 | Terry Brown |
| 2 | Tom Green |
| 3 | Kate White |
| | |

Currency
+----+-------------------+
| id | name |
+----+-------------------+
| 1 | EUR |
| 2 | USD |
| 3 | JPY |
| | |

And now I want to find all contracts which signed with customer named "Terry", like this:

Contract.where("customers.name like '%Terry%'").includes(:buyer,:user)
#I want 1 and 3, but it can only get 1
Contract.where("customers.name like '%Terry%'").includes(:user, :buyer)
#If I write "user" before "buyer", then I can only get 3

Someone told me it can work like this:

Contract.join(:customer).where("customers.name like '%terry%'").includes(:user,:buyer)
#It works fine.

I tried and it does work. But further when the Contract model belongs_to other model, such as currency_id, the method above cannot work again.

Contract.join(:customer).where("customers.name like '%terry%'").includes(:currency, :user, :buyer)
#>>Mysql2::Error: Unknown column 'customers_contracts.id' in 'field list': ...