Ruby on Rails - Primary and Foreign key

Question

Hey,

I am creating a site in Ruby on Rails, I have two models a User model and a Transaction model.

These models both belong to an account so they both have a field called account_id

I am trying to setup a association between them like so:

class User < ActiveRecord::Base
  belongs_to :account
  has_many :transactions
end

class Transaction < ActiveRecord::Base
  belongs_to :account
  belongs_to :user
end

I am using these associations like so:

user = User.find(1)
transactions = user.transactions

At the moment the application is trying to find the transactions with the user_id, here is the SQL it generates:

Mysql::Error: Unknown column 'transactions.user_id' in 'where clause': SELECT * FROM `transactions` WHERE (`transactions`.user_id = 1)

This is incorrect as I would like the find the transactions via the account_id, I have tried setting the associations like so:

class User < ActiveRecord::Base
  belongs_to :account
  has_many :transactions, :primary_key => :account_id, :class_name => "Transaction"
end

class Transaction < ActiveRecord::Base
  belongs_to :account
  belongs_to :user, :foreign_key => :account_id, :class_name => "User"
end

This almost achieves what I am looking to do and generates the following SQL:

Mysql::Error: Unknown column 'transactions.user_id' in 'where clause': SELECT * FROM `transactions` WHERE (`transactions`.user_id = 104)

The number 104 is the correct account_id but it is still trying to query the transaction table for a user_id field. Could someone give me some advice on how I setup the associations to query the transaction table for the account_id instead of the user_id resulting in a SQL query like so:

SELECT * FROM `transactions` WHERE (`transactions`.account_id = 104)

Cheers

Eef

Answer 1

class Account < ActiveRecord::Base
  has_many :users
  has_many :accounts
end

class User < ActiveRecord::Base
  has_many :transactions, :through => :account
end

http://api.rubyonrails.org/classes/ActiveRecord/Associations/ClassMethods.html#M001833

Answer 2

If you don't have the columnt user_id in the transactions table then you should use the Account model to select all transactions:

class User < ActiveRecord::Base
  belongs_to :account
end

class Account < ActiveRecord::Base
  has_many :transactions
  has_one :user # or has_many :users
end

class Transaction < ActiveRecord::Base
  belongs_to :account
end

User.find(1).account.transactions

Note that you have to remove has_many :transactions from User and belongs_to :user from Transaction as they assume that you do have the user_id column.