CODEWITHSUNDEEP
ruby-on-rails-3activerecordassociations
TerryChen
TerryChen

Reputation: 71

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

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

Upvotes: 5

Views: 362

Answers (2)

charlysisto
charlysisto

Reputation: 3700

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

Upvotes: 2

house9
house9

Reputation: 20624

have you tried using joins :buyer or :user instead of :customer?

your Contract model has no :customer attribute/relation

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

my guess is that will be equivalent to something like

Contract.join("INNER JOIN customers ON customers.id = contracts.buyer_customer_id").where("customers.name like '%terry%'").includes(:currency, :user, :buyer)

check your log file to see exactly what sql is being generated in each case - log/development.log

Upvotes: 0

Related Questions