2 belongs_to to the same parent table

Rails
1

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

2

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:

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

3

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

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

4

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

5

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