# 2 belongs\_to to the same parent table

**URL:** <https://www.ruby-forum.com/t/2-belongs-to-to-the-same-parent-table/74066>\
**Category:** Rails\
**Created:** [February 27, 2006, 9:02am UTC](https://www.ruby-forum.com/t/2-belongs-to-to-the-same-parent-table/74066 "2006-02-27T09:02:01Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![agathe](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/ccd318/32.png) [@agathe](https://www.ruby-forum.com/u/agathe)\
**Post date:** [February 27, 2006, 9:02am UTC](https://www.ruby-forum.com/t/2-belongs-to-to-the-same-parent-table/74066/1 "2006-02-27T09:02:01Z")

</div>

Hello!

I have 2 table: users and buddies  
User: id, name, …  
Buddy: id, user\_id, user\_buddy\_id, …

So if you have 2 users 1,jack and 2,fred and fred is a buddy of jack,  
there is a Buddy object: id=1, user\_id=1, user\_buddy\_id=2

I can declare only one belongs\_to in Buddy and one has\_many in User. And  
there is conflict if I had the second one (the first one is discarded)

class User  
has\_many :buddies, :foreign\_key =\> ‘user\_id’  
#has\_many :buddies, :foreign\_key =\> ‘user\_buddy\_id’ # does not work, if  
added as last, the previous relationship on buddies drops  
end

class Buddy  
belongs\_to :user, :foreign\_key =\> ‘user\_id’  
#belongs\_to :user, :foreign\_key =\> ‘user\_buddy\_id’ # does not work  
end

Is there a solution existing in Rails that solves this problem? Or is  
this design bad anyway?  
I cannot think of any elegant solution. Any advice welcome!!

Thnx!!

agathe

---

<div class="post-metadata">

**Author:** ![agathe](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/ccd318/32.png) [@agathe](https://www.ruby-forum.com/u/agathe)\
**Post date:** [February 27, 2006, 11:24am UTC](https://www.ruby-forum.com/t/2-belongs-to-to-the-same-parent-table/74066/2 "2006-02-27T11:24:06Z")

</div>

Am Montag, den 27.02.2006, 09:02 +0100 schrieb agathe agathe:

> I have 2 table: users and buddies  
> User: id, name, …  
> Buddy: id, user\_id, user\_buddy\_id, …
> 
> So if you have 2 users 1,jack and 2,fred and fred is a buddy of jack,  
> there is a Buddy object: id=1, user\_id=1, user\_buddy\_id=2

> belongs\_to :user, :foreign\_key =\> ‘user\_id’  
> #belongs\_to :user, :foreign\_key =\> ‘user\_buddy\_id’ # does not work  
> end
> 
> Is there a solution existing in Rails that solves this problem? Or is  
> this design bad anyway?  
> I cannot think of any elegant solution. Any advice welcome!!

Here is an interesting discussion about this kind of problem:

> **[SitePoint Forums | Web Development & Design Community](https://www.sitepoint.com/community/)**
>
> A community for web designers and developers to discuss everything from HTML, CSS, JavaScript, PHP, to Photoshop, SEO and more.

The solution is to do a self-join on your users table. If you have  
further questions let me know.

–  
Norman T.

[http://blog.inlet-media.de](http://blog.inlet-media.de)

---

<div class="post-metadata">

**Author:** ![agathe](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/ccd318/32.png) [@agathe](https://www.ruby-forum.com/u/agathe)\
**Post date:** [February 27, 2006, 11:44am UTC](https://www.ruby-forum.com/t/2-belongs-to-to-the-same-parent-table/74066/3 "2006-02-27T11:44:00Z")

</div>

Thanks!! I’m going to look into it

agathe

> Here is an interesting discussion about this kind of problem:
> 
> [http://www.sitepoint.com/forums/showthread.php?t=309718](http://www.sitepoint.com/forums/showthread.php?t=309718)
> 
> The solution is to do a self-join on your users table. If you have  
> further questions let me know.
> 
> –  
> Norman T.
> 
> [http://blog.inlet-media.de](http://blog.inlet-media.de)

---

<div class="post-metadata">

**Author:** ![agathe](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/ccd318/32.png) [@agathe](https://www.ruby-forum.com/u/agathe)\
**Post date:** [February 28, 2006, 10:42am UTC](https://www.ruby-forum.com/t/2-belongs-to-to-the-same-parent-table/74066/4 "2006-02-28T10:42:41Z")

</div>

So I got it to work as i wanted, until i realized that i cannot modify  
the attributes of the association because the association table has 2  
fields as primary key.

> > b = Buddy.find :first  
> > =\> #\<Buddy:0x3ba05c8 @attributes={“user\_buddy\_id”=\>“10”,  
> > “b\_profile\_id”=\>“0”, “user\_id”=\>“3”, “b\_date\_created”=\>nil,  
> > “b\_activated”=\>“0”}\>
> 
> > b.b\_activated = 1  
> > =\> 1
> 
> > b.save!  
> > ActiveRecord::StatementInvalid: #42S22Unknown column  
> > ‘user\_iduser\_buddy\_id’ in ‘where clause’: UPDATE buddies SET  
> > `b_profile_id` = 0, `b_date_created` = NULL, `user_buddy_id` = 10,  
> > `b_activated` = 1, `user_id` = 3 WHERE id = NULL

I haven’t found anything about declaring the primary key of a table to a  
set of keys.

Is the only solution to delete the record and create it again? or to  
make the sql statement directly?

thanks for the help!

agathe

---

<div class="post-metadata">

**Author:** ![agathe](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/ccd318/32.png) [@agathe](https://www.ruby-forum.com/u/agathe)\
**Post date:** [February 28, 2006, 4:56pm UTC](https://www.ruby-forum.com/t/2-belongs-to-to-the-same-parent-table/74066/5 "2006-02-28T16:56:21Z")

</div>

It seems to work now as i want. I only rewrote a method update in Buddy  
and created a ‘second\_key’. It’s not all perfect but it works for me.

To wrap up what i have:

The 2 tables:

users:  
-id  
-username  
-activated  
-…

buddy:  
-user\_id  
-user\_buddy\_id  
-b\_activated (0: at creation 1: when the user identified by  
user\_buddy\_id has accepted to be buddy of the user identified by  
user\_id)  
note: as you have a field also in user called ‘activated’, you have to  
use another name otherwise one is dropped, you cannot access to both  
‘activated’

The 2 models:

class User \< ActiveRecord::Base  
has\_and\_belongs\_to\_many :buddies,  
:join\_table =\> ‘buddies’,  
:foreign\_key =\> ‘user\_id’,  
:association\_foreign\_key =\> ‘user\_buddy\_id’,  
# i don’t need those because the relation is  
#directional:  
#:after\_add =\> :create\_reverse\_association,  
#:after\_remove =\> :remove\_reverse\_association,  
:class\_name =\> ‘User’,  
:conditions =\> ‘b\_activated=1’

# other convenient access

has\_and\_belongs\_to\_many :sent\_buddy\_requests,  
:join\_table =\> ‘buddies’,  
:foreign\_key =\> ‘user\_id’,  
:association\_foreign\_key =\> ‘user\_buddy\_id’,  
:class\_name =\> ‘User’,  
:conditions =\> ‘b\_activated=0’

has\_and\_belongs\_to\_many :received\_buddy\_requests,  
:join\_table =\> ‘buddies’,  
:foreign\_key =\> ‘user\_buddy\_id’,  
:association\_foreign\_key =\> ‘user\_id’,  
:class\_name =\> ‘User’,  
:conditions =\> ‘b\_activated=0’

has\_and\_belongs\_to\_many :is\_buddy\_of,  
:join\_table =\> ‘buddies’,  
:foreign\_key =\> ‘user\_buddy\_id’,  
:association\_foreign\_key =\> ‘user\_id’,  
:class\_name =\> ‘User’,  
:conditions =\> ‘b\_activated=1’

end

class Buddy \< ActiveRecord::Base

# I have added a ‘second\_key’ and copied more or less the code as it

came from

# the ‘primary\_key’. Only problem: in the update code

def self.second\_key  
reset\_second\_key  
end

def self.set\_second\_key( value=nil, &block )  
define\_attr\_method :second\_key, value, &block  
end

def reset\_second\_key  
key = ‘id2’

# I don’t understand what this code is doing:

# case primary\_key\_prefix\_type

# when :table\_name

# key =

Inflector.foreign\_key(class\_name\_of\_active\_record\_descendant(self),  
false)

# when :table\_name\_with\_underscore

# key =

Inflector.foreign\_key(class\_name\_of\_active\_record\_descendant(self))

# end

```
set_second_key(key)
key

```

end

set\_primary\_key “user\_id”  
set\_second\_key “user\_buddy\_id”

def update  
att\_quoted = attributes\_with\_quotes(false)  
att\_quoted.delete(self.class.second\_key)  
connection.update(  
"UPDATE #{self.class.table\_name} " +  
"SET #{quoted\_comma\_pair\_list(connection, att\_quoted)} " +  
“WHERE #{self.class.primary\_key} = #{quote(id)} " +  
" AND #{self.class.second\_key} = #{quote(user\_buddy\_id)}”,  
“#{self.class.name} Update”  
)  
end  
end

Now I can do this:

> > a = User.find\_by\_username ‘agathe’  
> > n = User.find\_by\_username ‘newthing’  
> > a.buddies.push\_with\_attributes(n,{:b\_activated =\> 0})  
> > n.received\_buddy\_requests.size  
> > =\> 1
> 
> > a.sent\_buddy\_requests[0]  
> > =\> #\<User:0x3b45008 @attributes={“user\_buddy\_id”=\>“11”,  
> > “username”=\>“newthing”, “id”=\>“11”, “user\_id”=\>“3”,  
> > “b\_activated”=\>“0”}\>
> 
> > n.received\_buddy\_requests[0]  
> > =\> #\<User:0x3b6d538 @attributes={“user\_buddy\_id”=\>“11”,  
> > “username”=\>“agathe”, “user\_id”=\>“3”, “id”=\>“3”, “b\_activated”=\>“0”}\>
> 
> > b= Buddy.find\_first  
> > =\> #\<Buddy:0x3aa38f0 @attributes={“user\_buddy\_id”=\>“11”, “user\_id”=\>“3”,  
> > “b\_activated”=\>“0”}\>
> 
> > b.b\_activated = 1  
> > =\> 1
> 
> > b.save!  
> > =\> true

Note: you never get a Buddy object if you do ie a.buddies[0], you get a  
user object, so the changes made on that object won’t be real. You need  
to make the changes on the Buddy object directly, i.e on b =  
Buddy.find\_first

heepee