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.