I have a problem declaring a relationship through a join_table ; the SQL
generated by ActiveRecord is correct, but there are two columns called
‘id’, and the final object gets the ‘wrong’ column …
Here’s a more detailed description of the data, code, and problem :-
There’s a People table, with id, name, and a few things.
Also a Relationships table, with id, subject, role, object.
subject and object are foreign keys into People.
The model declaration for ‘person’ says :-
class Person < ActiveRecord::Base
has_and_belongs_to_many :parents, :class_name=>“Person”,
:join_table=>“relationships”, :conditions=>“role =
‘parent’”,
:foreign_key=>“object”,
:association_foreign_key=>“subject”
has_and_belongs_to_many :children, :class_name=>"Person",
:join_table=>"relationships", :conditions=>"role =
‘parent’",
:foreign_key => “subject”,
:association_foreign_key=>“object”
and for ‘relationship’ is it :-
class Relationship < ActiveRecord::Base
belongs_to :subject, :class_name=>“Person”
belongs_to :object, :class_name=>“Person”
Neither of these queries are quite right – they give the correct
collection of Person objects, but each of these objects has the wrong
“id” value.
The development log reveals the SQL being used in each case (for
Person.id==1) :-
(warning; wide lines)
Parents (which works) :-
mysql> SELECT * FROM people INNER JOIN relationships ON people.id =
relationships.subject WHERE (relationships.object = 1 AND (role =
‘parent’));
±—±------------±----------------±---------±-------±---------------±---------------±—±--------±-------±-------±---------------±---------------+
| id | family_name | personal_name | known_as | gender | created_at | updated_at | id | subject | role | object | created_at | updated_at |
±—±------------±----------------±---------±-------±---------------±---------------±—±--------±-------±-------±---------------±---------------+
| 6 | Cheetham | Bryan Harold | | m | 20060513211519 | 20060513211519 | 5 | 6 | parent | 1 | 20060513211832 | 20060513211832 |
| 5 | Hanton | Glenna Patricia | | f | 20060513211509 | 20060513211509 | 8 | 5 | parent | 1 | 20060513211903 | 20060513211903 |
±—±------------±----------------±---------±-------±---------------±---------------±—±--------±-------±-------±---------------±---------------+
mysql> SELECT * FROM people INNER JOIN relationships ON people.id =
relationships.object WHERE (relationships.subject = 1 AND (role =
‘parent’)) ;
±—±------------±--------------------------±---------±-------±---------------±---------------±—±--------±-------±-------±---------------±---------------+
| id | family_name | personal_name | known_as | gender | created_at | updated_at | id | subject | role | object | created_at | updated_at |
±—±------------±--------------------------±---------±-------±---------------±---------------±—±--------±-------±-------±---------------±---------------+
| 3 | Cheetham | Alexander Benjamin Osborn | Alex | m | 20060513211442 | 20060513211442 | 1 | 1 | parent | 3 | 20060513211759 | 20060513211759 |
| 4 | Osborn | Katherine Freya Cheetham | Katie | f | 20060513211455 | 20060513211455 | 2 | 1 | parent | 4 | 20060513211806 | 20060513211806 |
±—±------------±--------------------------±---------±-------±---------------±---------------±—±--------±-------±-------±---------------±---------------+
In each query I can see two columns named ‘id’ – the first one is the
Person.id, which I want, and the second one is the Relationship.id,
which I do not. But it’s the second one being used to populate the final
Person object.
How do I state the model habtm to avoid this problem? If my
Relationships table had no id column I guess that would help, but I need
to be able to talk to it normally elsewhere in the app, and therefore I
think I should be keeping ‘id’ …
I think it has something to do with the warning in the habtm api
":join_table - specify the name of the join table if the default based
on lexical order isn’t what you want. WARNING: If you’re overwriting
the table name of either class, the table_name method MUST be declared
underneath any has_and_belongs_to_many declaration in order to work. "
But unfortunately I don’t understand what I should do …
-jim
but they didn’t 
which is probably more my fault than
I got that part of the message.