Joining tables but not on the default ID field

I've got a working app, at its core I have a rake task that pulls in the
resort data from a CSV file (which a data provider will give me
annually). Thanks to the help here it's all working fine. I think I've
made a basic error in the architecture of the app though and I'd like to
put it right at this early stage.

In short my data providers CSV file contains a serial_number field which
looks like this "574338". You can see my existing app records it in a
serial_number field of the Resort.

I have adverts too such with HABTM on resorts <-> adverts, as I say it's
al working as it but I think that what I need to do here is alter the
join table so that it links adverts to resorts on serial_number and not
on the resort ID. My concern here is that I want to be using the same
key field for resorts as my data provider and that's the serial_number.

So - how do I do it? I made several attempts, but it all gets quite
confusing, quite quickly.

I'd be very grateful for help, a lot of code with line numbers pasted
here. I'm guessing that the secret sauce is in lines, 5, 90, 196 and 197
(unsure if I have to touch the resort or advert migrations).

I've pastied the routes file as well for good measure.

http://pastie.org/581103

Be grateful for help here :-).

bb

So if I've understood you correctly,

I could just edit this

in advert.rb
has_and_belongs_to_many :resorts

in resort.rb
has_and_belongs_to_many :adverts

to include , :foreign_key => "serial_number"

Which one (or both) do I specify the foreign key on and importantly does
the join table migration work as it is?

a bit more complicated with a has_and_belongs_to_many - there's
check the docs). not sure what you mean about the migration working.

Fred

Hi Fred,

I mean lines 196 / 197 in the pastie here http://pastie.org/581103 - re
the migration that sets up the join table.

Do you see what I'm trying to achieve? The serial_number field is unique
and each resort has one.

bb

Hi Fred,

I mean lines 196 / 197 in the pastie herehttp://pastie.org/581103- re
the migration that sets up the join table.

Do you see what I'm trying to achieve? The serial_number field is unique
and each resort has one.

I've realised what you're trying to do - you want the columns in the
join tables to refer to a column other than the id column in the
parent tables. I'm not sure habtm supports that.

Fred

ahhh, ok, maybe I can go about this a different way or maybe just forget
about it !
Strange, I thought this might be doable and in these circumstances the
best thing to do.