# Joining tables but not on the default ID field **URL:** \ **Category:** Rails\ **Created:** [August 12, 2009, 12:06pm UTC](https://www.ruby-forum.com/t/joining-tables-but-not-on-the-default-id-field/173636 "2009-08-12T12:06:54Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![bingobob](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/e480ec/32.png) [@bingobob](https://www.ruby-forum.com/u/bingobob)\ **Post date:** [August 12, 2009, 12:06pm UTC](https://www.ruby-forum.com/t/joining-tables-but-not-on-the-default-id-field/173636/1 "2009-08-12T12:06:54Z")
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](http://pastie.org/581103) Be grateful for help here :-). bb ---
**Author:** ![bingobob](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/e480ec/32.png) [@bingobob](https://www.ruby-forum.com/u/bingobob)\ **Post date:** [August 12, 2009, 1:23pm UTC](https://www.ruby-forum.com/t/joining-tables-but-not-on-the-default-id-field/173636/2 "2009-08-12T13:23:38Z")
I guess you can do it as long as you explicitly specify the foreign key in your model. For example, let us now try and join Category Table and Subcategory Table using ‘serial\_no’ instead of ‘id’. class Category \< ActiveRecord::Base has\_many :subcategories, :foreign\_key =\> “serial\_no” end class Subcategory \< ActiveRecord::Base belongs\_to :category, :foreign\_key =\> “serial\_no” end ---
**Author:** ![bingobob](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/e480ec/32.png) [@bingobob](https://www.ruby-forum.com/u/bingobob)\ **Post date:** [August 12, 2009, 1:38pm UTC](https://www.ruby-forum.com/t/joining-tables-but-not-on-the-default-id-field/173636/3 "2009-08-12T13:38:18Z")
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? ---
**Author:** ![bingobob](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/e480ec/32.png) [@bingobob](https://www.ruby-forum.com/u/bingobob)\ **Post date:** [August 12, 2009, 1:57pm UTC](https://www.ruby-forum.com/t/joining-tables-but-not-on-the-default-id-field/173636/4 "2009-08-12T13:57:43Z")
On Aug 12, 12:38 pm, bingo bob [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote: > 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 a :foreign\_key and :association\_foreign\_key (one for each ‘side’ - check the docs). not sure what you mean about the migration working. Fred ---
**Author:** ![bingobob](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/e480ec/32.png) [@bingobob](https://www.ruby-forum.com/u/bingobob)\ **Post date:** [August 12, 2009, 2:03pm UTC](https://www.ruby-forum.com/t/joining-tables-but-not-on-the-default-id-field/173636/5 "2009-08-12T14:03:36Z")
Hi Fred, I mean lines 196 / 197 in the pastie here [http://pastie.org/581103](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 ---
**Author:** ![bingobob](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/e480ec/32.png) [@bingobob](https://www.ruby-forum.com/u/bingobob)\ **Post date:** [August 12, 2009, 3:15pm UTC](https://www.ruby-forum.com/t/joining-tables-but-not-on-the-default-id-field/173636/6 "2009-08-12T15:15:34Z")
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. ---
**Author:** ![bingobob](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/e480ec/32.png) [@bingobob](https://www.ruby-forum.com/u/bingobob)\ **Post date:** [August 12, 2009, 2:50pm UTC](https://www.ruby-forum.com/t/joining-tables-but-not-on-the-default-id-field/173636/7 "2009-08-12T14:50:25Z")
On Aug 12, 1:03 pm, bingo bob [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote: > 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