Quick question. Say I have a geographical database with counties and zip
codes where counties have and belong to many zip codes.
zip_codes (id, zip_code)
counties (id, name)
When I create the association table, the Rails way says to do the
following: counties_zip_codes (county_id, zip_code_id).
However, given that zip_codes.zip_code is itself a candidate key, I
would much prefer to do the following: counties_zip_codes (county_id,
zip_code), as this removes the need to join with the 42K record
zip_codes table.
However, when I do the following in the my model:
class ZipCode < Active....
has_and_belongs_to_many :counties, :foreign_key => "zip_code"
class County < Active...
has_and_belongs_to_many :zip_codes, :association_foreign_key =>
"zip_code"
AR still uses ZipCode.id when I call county.zip_codes << some_zip,
instead of ZipCode.zipcode, which is the preferred behavior.
Am I doing something wrong or is AR ignoring me? To solve the problem i
simply made the ID of the ZipCode equal to the ZipCode, but I would like
to know what I''m doing wrong.
Thanks in advance.
--
Posted via http://www.ruby-forum.com/.
It''s not going to work because you only have the ids in your bridge
table.
(country_id, zip_code_id)
The association of zip to country is done via the join table. Remove the
:foreign_key and :association_foreign_key from your model declaration
Then you should be able to do
@country = Country.find_by_name "United States"
@zip_codes = @country.zip_codes
for zip in @zip_codes
zip.zip_code
end
Add a new one like this:
@country.zip_codes << ZipCode.create :zip_code => "55123"
I suggest reading up on HABTM a little more in the Agile book or look at the
API docs. What you were doing is a little overkill.
I hope that helps you out! Good luck!
On 6/16/06, emo <eimorton@gmail.com> wrote:>
> Quick question. Say I have a geographical database with counties and zip
> codes where counties have and belong to many zip codes.
>
> zip_codes (id, zip_code)
> counties (id, name)
>
> When I create the association table, the Rails way says to do the
> following: counties_zip_codes (county_id, zip_code_id).
>
> However, given that zip_codes.zip_code is itself a candidate key, I
> would much prefer to do the following: counties_zip_codes (county_id,
> zip_code), as this removes the need to join with the 42K record
> zip_codes table.
>
> However, when I do the following in the my model:
>
> class ZipCode < Active....
> has_and_belongs_to_many :counties, :foreign_key =>
"zip_code"
>
> class County < Active...
> has_and_belongs_to_many :zip_codes, :association_foreign_key =>
> "zip_code"
>
> AR still uses ZipCode.id when I call county.zip_codes << some_zip,
> instead of ZipCode.zipcode, which is the preferred behavior.
>
> Am I doing something wrong or is AR ignoring me? To solve the problem i
> simply made the ID of the ZipCode equal to the ZipCode, but I would like
> to know what I''m doing wrong.
>
> Thanks in advance.
>
> --
> Posted via http://www.ruby-forum.com/.
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL:
http://wrath.rubyonrails.org/pipermail/rails/attachments/20060616/0075fee9/attachment.html
emo wrote:> Quick question. Say I have a geographical database with counties and zip > codes where counties have and belong to many zip codes. > > zip_codes (id, zip_code) > counties (id, name) > > When I create the association table, the Rails way says to do the > following: counties_zip_codes (county_id, zip_code_id). > > However, given that zip_codes.zip_code is itself a candidate key, I > would much prefer to do the following: counties_zip_codes (county_id, > zip_code), as this removes the need to join with the 42K record > zip_codes table. > > However, when I do the following in the my model: > > class ZipCode < Active.... > has_and_belongs_to_many :counties, :foreign_key => "zip_code" > > class County < Active... > has_and_belongs_to_many :zip_codes, :association_foreign_key => > "zip_code" > > AR still uses ZipCode.id when I call county.zip_codes << some_zip, > instead of ZipCode.zipcode, which is the preferred behavior. > > Am I doing something wrong or is AR ignoring me? To solve the problem i > simply made the ID of the ZipCode equal to the ZipCode, but I would like > to know what I''m doing wrong.I think AR is ignoring you. You can tell habtm to use a non-standard foreign key name in the join table, but it will still use the id of the ZipCode object to populate the table, which for your case is wrong. You should be able to use the :finder_sql, :delete_sql and :insert_sql options on habtm to tell it to change how it uses the join table fields and which field of the ZipCode to use to populate the table. Another approach would be to use a has_many :through association. The table of your join model (call it a ZipMapping) could contain county_id, zip_code_id, and zip_code. It would then be easy to use the has_many association in County to find all counties with a certain zip_code, or all the zip_codes in the county - no join with the zip_codes table required. Maintaining the zip_code_id in the association lets you more easily manage things if you need to change zip codes (say you want to convert from 5 to 9 digit codes). -- Josh Susser http://blog.hasmanythrough.com -- Posted via http://www.ruby-forum.com/.
Thanks, this was helpful. I ended up making zip_codes.id a non-auto incrementing field that holds the actual "zip code". I see plusses and minuses to this approach: On the plus side I never have to join with the 42,000 record zip_codes table (which will increase in size with canadian postal codes) On the neutral side I need strict foreign key relationships with cascade so that I can change zip_codes when they change (which apparently they do). Thanks for the help. Josh Susser wrote:> emo wrote: >> Quick question. Say I have a geographical database with counties and zip >> codes where counties have and belong to many zip codes. >> >> zip_codes (id, zip_code) >> counties (id, name) >> >> When I create the association table, the Rails way says to do the >> following: counties_zip_codes (county_id, zip_code_id). >> >> However, given that zip_codes.zip_code is itself a candidate key, I >> would much prefer to do the following: counties_zip_codes (county_id, >> zip_code), as this removes the need to join with the 42K record >> zip_codes table. >> >> However, when I do the following in the my model: >> >> class ZipCode < Active.... >> has_and_belongs_to_many :counties, :foreign_key => "zip_code" >> >> class County < Active... >> has_and_belongs_to_many :zip_codes, :association_foreign_key => >> "zip_code" >> >> AR still uses ZipCode.id when I call county.zip_codes << some_zip, >> instead of ZipCode.zipcode, which is the preferred behavior. >> >> Am I doing something wrong or is AR ignoring me? To solve the problem i >> simply made the ID of the ZipCode equal to the ZipCode, but I would like >> to know what I''m doing wrong. > > I think AR is ignoring you. You can tell habtm to use a non-standard > foreign key name in the join table, but it will still use the id of the > ZipCode object to populate the table, which for your case is wrong. You > should be able to use the :finder_sql, :delete_sql and :insert_sql > options on habtm to tell it to change how it uses the join table fields > and which field of the ZipCode to use to populate the table. > > Another approach would be to use a has_many :through association. The > table of your join model (call it a ZipMapping) could contain county_id, > zip_code_id, and zip_code. It would then be easy to use the has_many > association in County to find all counties with a certain zip_code, or > all the zip_codes in the county - no join with the zip_codes table > required. Maintaining the zip_code_id in the association lets you more > easily manage things if you need to change zip codes (say you want to > convert from 5 to 9 digit codes). > > -- > Josh Susser > http://blog.hasmanythrough.com-- Posted via http://www.ruby-forum.com/.