# Problems with multiple 'id' columns in a join\_table

**URL:** <https://www.ruby-forum.com/t/problems-with-multiple-id-columns-in-a-join-table/60613>\
**Category:** Rails\
**Created:** [May 16, 2006, 4:15am UTC](https://www.ruby-forum.com/t/problems-with-multiple-id-columns-in-a-join-table/60613 "2006-05-16T04:15:34Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![jimc](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/ba8739/32.png) [@jimc](https://www.ruby-forum.com/u/jimc)\
**Post date:** [May 16, 2006, 4:15am UTC](https://www.ruby-forum.com/t/problems-with-multiple-id-columns-in-a-join-table/60613/1 "2006-05-16T04:15:34Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![jimc](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/ba8739/32.png) [@jimc](https://www.ruby-forum.com/u/jimc)\
**Post date:** [May 16, 2006, 4:22am UTC](https://www.ruby-forum.com/t/problems-with-multiple-id-columns-in-a-join-table/60613/2 "2006-05-16T04:22:52Z")

</div>

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/20/many-to-many-dance-off)  
[http://blog.hasmanythrough.com/articles/2006/04/21/self-referential-through](http://blog.hasmanythrough.com/articles/2006/04/21/self-referential-through)

–  
Josh S.  
[http://blog.hasmanythrough.com](http://blog.hasmanythrough.com)

---

<div class="post-metadata">

**Author:** ![jimc](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/ba8739/32.png) [@jimc](https://www.ruby-forum.com/u/jimc)\
**Post date:** [May 16, 2006, 6:42am UTC](https://www.ruby-forum.com/t/problems-with-multiple-id-columns-in-a-join-table/60613/3 "2006-05-16T06:42:25Z")

</div>

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/20/many-to-many-dance-off)  
> [http://blog.hasmanythrough.com/articles/2006/04/21/self-referential-through](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

---

<div class="post-metadata">

**Author:** ![jimc](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/ba8739/32.png) [@jimc](https://www.ruby-forum.com/u/jimc)\
**Post date:** [May 16, 2006, 12:48pm UTC](https://www.ruby-forum.com/t/problems-with-multiple-id-columns-in-a-join-table/60613/4 "2006-05-16T12:48:57Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![jimc](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/ba8739/32.png) [@jimc](https://www.ruby-forum.com/u/jimc)\
**Post date:** [May 16, 2006, 10:09am UTC](https://www.ruby-forum.com/t/problems-with-multiple-id-columns-in-a-join-table/60613/5 "2006-05-16T10:09:22Z")

</div>

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](http://blog.hasmanythrough.com)