using custom foreign keys in associations

[Arun Srini]

Hi
I have following models

class Room < ActiveRecord::Base
belongs_to :roomtype, :foreign_key=>'t_name'
end

class Roomtype < ActiveRecord::Base
has_many :rooms
accepts_nested_attributes_for :rooms
end

and the following tables


class CreateRoomtypes < ActiveRecord::Migration
create_table :roomtypes do |t|
t.string :t_name
t.string :rate
t.string :comment

t.timestamps
end
end
class CreateRooms < ActiveRecord::Migration
create_table :rooms do |t|
t.string :name
t.boolean :avail
t.string :type_name - to reference Roomtype's name

t.timestamps
end
end

I want to use type_name column of Rooms as foreign key to Roomtype
model. I tried running below queries but in vein. What am I doing
wrong? How to 'plumb these together'?

ruby-1.9.2-p0 > Roomtype.first
=> #<Roomtype id: 1, t_name: "type1", rate: "100", comment: nil,
created_at: "2010-10-19 05:33:50", updated_at: "2010-10-19 05:33:50">

ruby-1.9.2-p0 > Room.first
=> #<Room id: 1, name: "201", avail: true, created_at: "2010-10-19
05:33:50", updated_at: "2010-10-19 05:44:32", type_name: "type1">

ruby-1.9.2-p0 > Room.first.roomtype
=> nil
--i was expecting type1 here since the foreign key was t_name which
has type1 value.

[Arun Srini]

I found I needed to use :primary_key => 'type_name' in the room model.
I come from DB world and I know a primary key is one that shouldn't be
duplicated, so I am confused why I am telling the foreign key as
primary_key in rails and the reference column in the parent as
foreign_key.

[Frederick Cheung]

On Oct 19, 7:17 am, Arun Srini <arunro...@gmail.com> wrote:
> I found I needed to use :primary_key => 'type_name' in the room model.
> I come from DB world and I know a primary key is one that shouldn't be
> duplicated, so I am confused why I am telling the foreign key as
> primary_key in rails and the reference column in the parent as
> foreign_key.
>

Normally in a rails association the foreign key for a belongs to
always refers to the other table's primary key. If you want it to
refer to a different column then, as you discovered, you use the
primary_key option.

Fred

[Arun Srini]

I think here room's(associator for belongs_to) primary_key refers to
room_type(associated) foreign_key. This was why I got confused. If the
code was like
class room
belongs_to :room_type, foreign_key => 'type_name', refers_to =>
't_name' #(t_name - column in room_type class)
end

This would've been cleaner to think that the room's type_name refers
to room_type's t_name column.
Anyway.. me just getting to know if you don't do it the rails way, its
going to be tough way....

On Oct 19, 2:43 am, Frederick Cheung <frederick.che...@gmail.com>
wrote:

[Ar Chron]

Arun Srini wrote in post #955342:

>
> class CreateRoomtypes < ActiveRecord::Migration
> create_table :roomtypes do |t|
> t.string :t_name
> t.string :rate
> t.string :comment
> t.timestamps
> end
> end
> class CreateRooms < ActiveRecord::Migration
> create_table :rooms do |t|
> t.string :name
> t.boolean :avail
> t.string :type_name - to reference Roomtype's name
>
> t.timestamps
> end
> end
>
> I want to use type_name column of Rooms as foreign key to Roomtype
> model. I tried running below queries but in vein. What am I doing
> wrong? How to 'plumb these together'?
>

Since you do seem to be defining the rooms table, why not use the rails
'traditional' roomtypes_id field as your key? It is much clearer (I
know what that field is, and I know where it connects to), it is the
'convention over configuration' solution, and it alleviates any
potential issue with someone in the future deciding that "Oh, that room
type name doesn't really fit anymore, can I change it?" - I personally
abhor descriptive strings as keys for precisely that reason...

Leave some of that DB world training behind and embrace the 'rails way',
you'll be much happier, and things tend to fit more naturally.

--
Posted via http://www.ruby-forum.com/.

[Arun Srini]

Your argument was very fine, and I accept that. I am a hardcore DBA
and SQL expert, with good data modelling experience, and have come
across many models that needs to have multiple keys as prmary and
foreign. Thus my quest to custom foreign keys.
Anyway, the 'magic' rails does is very interesting..

[Marnen Laibow-Koser]

Arun Srini wrote in post #955420:

> Your argument was very fine, and I accept that. I am a hardcore DBA
> and SQL expert, with good data modelling experience,

If that were true, then you'd know that your original schema is not
normalized to 3NF and should not be used for general purposes.

> and have come
> across many models that needs to have multiple keys as prmary and
> foreign. Thus my quest to custom foreign keys.

You don't need custom foreign keys for this. Just normalize your DB
schema properly and you'll most likely get something that Rails can work
with.

> Anyway, the 'magic' rails does is very interesting..

Best,
--
Marnen Laibow-Koser
http://www.marnen.org
mar...@marnen.org

[Arun Srini]

On Oct 19, 9:06 am, Marnen Laibow-Koser <li...@ruby-forum.com> wrote:
> Arun Srini wrote in post #955420:
>
> > Your argument was very fine, and I accept that. I am a hardcore DBA
> > and SQL expert, with good data modelling experience,
>
> If that were true, then you'd know that your original schema is not
> normalized to 3NF and should not be used for general purposes.

3NF - every non prime attribute int he table should be directly
dependent on candidate key. I have room_type that is not directly
dependent on key(or even storing the motel info in central table), and
thus it's not in 3rd NF. I understand that , no need of any personal
comments here.

>
> > and have come
> > across many models that needs to have multiple keys as prmary and
> > foreign. Thus my quest to custom foreign keys.
>
> You don't need custom foreign keys for this.  Just normalize your DB
> schema properly and you'll most likely get something that Rails can work
> with.

I don't need custom foreign keys here, all I was tellign was that
there have been some cases where I needed more than one column to
refer to parent column and wanted to do this in rails.

[Susan Henesy]

I have been up all night trying to solve this same problem, and I
finally got it to work.

I'll use your tables to demonstrate what I did::

===================================

class Room < ActiveRecord::Base
belongs_to :roomtype

end

class Roomtype < ActiveRecord::Base

has_many :rooms,
:primary_key => "t_name",
:foreign_key => "type_name"
end
===================================

I tried this oh-so-many different ways, and the solution was as simple
as this. Ideally, I would prefer to set the tables up so that they all
receive the built-in Rails magic, but we have some legacy business logic
where I can only relate some of the data we import (let's say we import
it into "Room") on an old field that's a string datatype.

It's so great to see this working. I am able to write methods in my
model, and use it in web pages.

I hope this helps someone!