How to setup foreign keys for models

Question

I am having difficulties finding the right combination to setup my foreign keys. No matter what I do my console is still reading zip_ids when it should be zip_codes only.

I am receiving this in the console:

Zip.code(30052).users.count
  Zip Load (12.5ms)  SELECT `zips`.* FROM `zips` WHERE `zips`.`code` = 30052 LIMIT 1
   (0.3ms)  SELECT COUNT(*) FROM `users` WHERE `users`.`zip_code` = 12859
 => 0

The issue is the second line where it is somehow pulling zip code 12859, which I never specified. This is because it is using zip_id instead of zip_code from the user table.

zip.rb:

 has_many :users,  :foreign_key => "zip_code"

user.rb :

  belongs_to :zip

Damien · Accepted Answer · 2013-09-11 14:41:31Z

1

Set the code as your primary key

class Zip < ActiveRecord::Base
  self.primary_key = 'code'
  has_many :users, foreign_key: 'zip_code'
end

Don't forget to add a unique index to the code column:

class AddIndexToZipsCode < ActiveRecord::Migration
  def change
    add_index :zips, :code, unique: true
  end
end

And validate the uniqueness of code

# zip.rb
validates :code, uniqueness: true

Then you will find a zip Zip.find(code)

Add an index to zip_code on your users table.

class AddIndexToUsersZipCode < ActiveRecord::Migration
  def change
    add_index :users, :zip_code
  end
end

Now, Zip.find(code).users.count should work.

To get the reverse relationship working (user.zip), you need to specify the foreign_key in the association:

# user.rb
belongs_to :zip, foreign_key: :zip_code

edited Sep 11, 2013 at 14:41

answered Sep 11, 2013 at 13:56

Damien

27.6k7 gold badges42 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Cornelius Wilson Over a year ago

I did that prior to coming here. If I have both foreign keys then I get the same issue. If I remove foreign key from zips I get, "Mysql2::Error: Unknown column 'users.zip_id' in 'where clause': SELECT COUNT(*) FROM users WHERE users.zip_id = 12859".

Damien Over a year ago

That's strange... Do you care about the id of zip? Otherwise, you may want to use code as the primary_key. So your users table has a zip_id column as well as a zip_code? Maybe that's what is confusing Rails...

Cornelius Wilson Over a year ago

I wouldn't think adding a new column should be a requirement for it to work. I don't care for the id of the zip at all, and I'm about to remove it from the table. Which removal now gives zip_code = NULL

Cornelius Wilson Over a year ago

Thank you. Will have to study this some more to understand it in full.

Collectives™ on Stack Overflow

How to setup foreign keys for models

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related