How should I write the ActiveRecord when there are multiple associations?

Question

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': ...

Answer 1

That's because you're not supposed to use joins in conjonction with includes. It hasn't been emphasized enough (and there's no warning in rails) but

select, joins, group, having, etc. DO NOT WORK WITH includes!

You might get results but only by chance. And the odds are it'll break sooner than later.

It seems there's also some inconsistency with includes...

If you need to use a conventional outer join with activerecord >= 3.0 (which is the case here) use the excellent squeel gem. It really yields the power of Arel.

Try out (with squeel installed) :

Contract.joins{buyer.outer}.joins{user.outer}.where("name like '%terry%'")

The out of the box join does inner joining only, which excludes non intersecting table, making your goal impossible here : buyer & user can be mutually exclusive...