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