I'm having trouble defining associations in has_many :through using ruby on rails

Question

Here is a problem I've been struggling with for a while, and I know how to solve it with find_by_sql, but I just feel soooo close to being able to set up this association without needing to do any specific SQL, so here I am asking for help. Here is the situation:

1. 3 models: City, Zipcode, Contact
2. City has_many zipcodes
3. Zipcode belongs_to city, has_many contacts
4. Contact belongs_to Zipcode
5. Zipcode has attribute zip that contains the zip code. Contact also has a zip attribute.

So a city, like Atlanta, would have many zip codes, and each zip code has many contacts with that zip code in their address. The goal is that I could do this: @city.contacts and use the zipcodes as a join table.

Here are my model definitions:

class City < ActiveRecord::Base
  has_many :contacts, :through => :zipcodes #this does not work
end

class Contact < ActiveRecord::Base
  belongs_to :zipcode, :foreign_key => :zip, :primary_key => :zip
end

class Zipcode < ActiveRecord::Base
  belongs_to :city
  has_many :contacts, :primary_key => :zip, :foreign_key => :zip
end

And below is what comes from the console to test these associations. Every association works EXCEPT the city.contacts one. You can see the query it generates: it is c orrect, except right before the WHERE zipcodes.id should be zipcodes.zip. When you make that change, this query pulls up the proper records for the association. But h ow can I define the associations such that the query is formed properly? I've spent a couple hours on this.

zip = Zipcode.first Zipcode Load (0.2ms) SELECT zipcodes.* FROM zipcodes LIMIT 1 => #

zip.city City Load (0.5ms) SELECT cities.* FROM cities WHERE (cities.id = 7) ORDER BY name LIMIT 1 => #

zip.contacts Contact Load (0.3ms) SELECT contacts.* FROM contacts WHERE (contacts.zip = 30084) => [#

acity = City.find 7 City Load (0.3ms) SELECT cities.* FROM cities WHERE (cities.id = 7) ORDER BY name LIMIT 1 => #

acity.zipcodes Zipcode Load (0.2ms) SELECT zipcodes.* FROM zipcodes WHERE (zipcodes.city_id = 7) => [#

acity.contacts Contact Load (0.3ms) SELECT contacts.* FROM contacts INNER JOIN zipcodes ON contacts.zip = zipcodes.id WHERE ((zipcodes.city_id = 7)) => []

NOTE THE EMPTY RESULT ABOVE. KHAAAAAAN!

Answer 1

I think you have made life a little complicated for yourself. Try removing the :foreign_key, :primary_key declarations and start basic with rails doing the work. If the tables are set up correctly it should work without any problems. Let us know how you get on. All the best.

Answer 2

Typically, a has_many :through relationship is for a many-to-many relationship. In such a case, each city will have many contacts (true), and each contact would have many cities (not true?). You should probably reconsider this relationship definition. Since it seems like a contact will only belong to a city because of it's zip code, you should just use the cascading relationship of a city having many zip codes, and the zip code having many contacts.

However, I do understand the desire for wanting to collect all of a city's contacts from all of its zipcodes in an easy way. Though I haven't tested this, there are some errors in your setup of the above model.

When doing a has_many :through association, the model that has many through must also have many of the through object. As in the city-contacts-zipcodes example, you might try this:

class City < ActiveRecord::Base
  has_many :zipcodes
  has_many :contacts, :through => :zipcodes #this does not work
end

Then, in your zipcode model:

class Zipcode < ActiveRecord::Base
  belongs_to :city
  has_many :contacts
end

And your contact model:

class Contact < ActiveRecord::Base
  belongs_to :zipcode

  #not sure about this part
  belongs_to :city, :through => :zipcode
end

You really should need to specify foreign keys unless the variable name doesn't match the class name on these.

If this fails, you could simply remove the :through argument and write a method in your City class that will build an array of contacts something like this:

def contacts
  contacts = []
  self.zipcodes.each do |z|
    contacts += z.contacts.all
  end
end

Let me know which, if either of these work.