Rails - May 2006 - Problems with multiple ''id'' columns in a join_table

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

Jim Cheetham wrote:
> 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 ...
> ...
> 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'' ...
Take a look at these, they might help:
http://blog.hasmanythrough.com/articles/2006/04/20/many-to-many-dance-off
http://blog.hasmanythrough.com/articles/2006/04/21/self-referential-through

--
Josh Susser
http://blog.hasmanythrough.com

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

On Tue, May 16, 2006 at 04:22:52AM +0200, Josh Susser
wrote:
> Jim Cheetham wrote:
> > 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 ...
> 
> Take a look at these, they might help:
> http://blog.hasmanythrough.com/articles/2006/04/20/many-to-many-dance-off
> http://blog.hasmanythrough.com/articles/2006/04/21/self-referential-through
I did :-) but they didn''t :-( which is probably more my fault than
yours.

I thought I''d got a suitably nice set of declarations, but in practice
I
keep on seeing complaints about invalid source reflection, or an
application crash :-(

So another hint would be appreciated.

-jim

Jim Cheetham wrote:
>
> So another hint would be appreciated.
If you are using has_and_belongs_to_many, you shouldn''t have an id
field
in the join table.  If you need the id field in the join table, which 
implies you are storing extra attributes there, use has_many :through.

--
Josh Susser
http://blog.hasmanythrough.com

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

On Tue, May 16, 2006 at 10:09:22AM +0200, Josh Susser
wrote:
> If you are using has_and_belongs_to_many, you shouldn''t have an id
field
> in the join table.  If you need the id field in the join table, which 
> implies you are storing extra attributes there, use has_many :through.
Ah, yes :-) I got that part of the message.

I thought I''d understood the idea, and established a has_many from
People to Relationships, and then tried to set up another has_many going
:through that back out to People again. I''ll spare you the precise
details of what I tried, because that was a few edits ago and I didn''t
preserve it. However, there was a fatal complaint about a missing
:source that I couldn''t satisfy.

But reading into habtm options a little further revealed that I could
use :select to drop the entire Relationships table (and therefore the
conflicting ID column). This now works fine for me.

  # If p is the object of a relationship, p is a child (ATM)
  has_and_belongs_to_many :children, 
:class_name=>''Person'',
          :join_table=>:relationships,
          :foreign_key=>:subject, :association_foreign_key=>:object,
          :select=>"people.*",
:conditions=>"role=''parent''"

There may well be a neater, DRYer way to express my table
interrelationships, but at the moment this will do. Thanks for the
references; I''ll keep trying for my "aha!" moment :-)

-jim