Problems with multiple 'id' columns in a join_table


#1

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


#2

Jim C. 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 S.
http://blog.hasmanythrough.com


#3

On Tue, May 16, 2006 at 04:22:52AM +0200, Josh S. wrote:

Jim C. 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 :slight_smile: but they didn’t :frowning: 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 :frowning:

So another hint would be appreciated.

-jim


#4

On Tue, May 16, 2006 at 10:09:22AM +0200, Josh S. 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 :slight_smile: 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 :slight_smile:

-jim


#5

Jim C. 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 S.
http://blog.hasmanythrough.com