Forum: Ruby on Rails Problems with multiple 'id' columns in a join_table

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Jim C. (Guest)
on 2006-05-16 06:15
(Received via mailing list)
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
Josh S. (Guest)
on 2006-05-16 06:22
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...
http://blog.hasmanythrough.com/articles/2006/04/21...

--
Josh S.
http://blog.hasmanythrough.com
Jim C. (Guest)
on 2006-05-16 08:42
(Received via mailing list)
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...
> http://blog.hasmanythrough.com/articles/2006/04/21...

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
Josh S. (Guest)
on 2006-05-16 12:09
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
Jim C. (Guest)
on 2006-05-16 14:48
(Received via mailing list)
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 :-) 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
This topic is locked and can not be replied to.