one table, many-to-many

[dilbo]

I have a database where one table is users, users can know other users so that there will be a many to many relation of which users know what other users. ActiveRecord assumes the names in the join table is the table+_id. If I was gonna follow ActiveRecords naming this would be:
create table users_users(
user_id int,
user_id int,
primary key(user_id, user_id)
);
Now that obviously won't work, so how do I deal with this?

[liquidautumn]

maybe you could add something like 'user_to_user' reference table with user_id and referenced_user_id fields to maintain many to many relationship

[Luke Redpath]

This should work (not tested it though):

CREATE TABLE users_known_users (
  user_id INT,
  known_user_id INT,
  PRIMARY KEY(user_id, known_user_id)
);

class User < ActiveRecord::Base
  has_and_belongs_to_many :known_users,
                                       :join_table => 'users_known_users',
                                       :foreign_key => 'user_id',
                                       :association_foreign_key => 'known_user_id'
end

Now, I'm not 100% sure, but Rails might actually be able to work out the foreigh and association foreign keys by itself given the above schema, so you might be able to omit the last two keys of the habtm declaration - give it a try.

Example Usage:

# Controller:
@joe_bloggs = User.find(:name => 'Joe Bloggs')

# View: 
<% @joe_bloggs.known_users.each do |known_user| %>
  Joe Bloggs knows <%= known_user.name %>
<% end %>

[dilbo]

I'll try this, thanks very much

[samsm]

I'd love to be proved wrong, but a month or so ago I barked up the this tree for hours and I'm totally convinced it cannot be done with has_and_belongs_to_many. It's a limitation of that option.

Still, I'd love to be proved wrong.

The Agile Rails book suggests a combo of belongs_to and has_many on the same table, that may work for you.

[Luke Redpath]

Originally Posted by samsm

I'd love to be proved wrong, but a month or so ago I barked up the this tree for hours and I'm totally convinced it cannot be done with has_and_belongs_to_many. It's a limitation of that option.

Still, I'd love to be proved wrong.

The Agile Rails book suggests a combo of belongs_to and has_many on the same table, that may work for you.

Did you read my post?

To the OP, did you try it?

[samsm]

Originally Posted by Luke Redpath

Did you read my post?

Yes, and I'm pretty sure I tried that.

If you are representing to me that that works, I would love to try it again (wouldn't be the first time I thought I incorrectly eliminated a possibility), but I have wasted so ... much ... time ... on this already, I can't bare to proactively test it without some assurance that it will work.

[Luke Redpath]

Perhaps the OP could let us know.

[jpease]

Originally Posted by dilbo

I have a database where one table is users, users can know other users so that there will be a many to many relation of which users know what other users. ActiveRecord assumes the names in the join table is the table+_id. If I was gonna follow ActiveRecords naming this would be:
create table users_users(
user_id int,
user_id int,
primary key(user_id, user_id)
);
Now that obviously won't work, so how do I deal with this?

I'm no DB expert, but the very premise of what you are attempting to do seems flawed to me. As I far as I can tell from your post you have a SINGLE table called "users".

First, I don't think you make a join table from a single table. So that's probably why you can't do it in ActiveRecord. By definition, you make a join table when you "join" two tables. If you want results from a single table you execute a query against that table.

Second, if you are just working with a single table you do not have a many-to-many relationship. A many-to-many relationship is where you have 2 tables and one row in Table A is related to one or more rows in Table B AND one row in Table B is related to one or more rows in Table A. A one-to-anything relationship requires that there be more than one table. Otherwise you just have a "one" relationship, and that ain't no relationship at all!

I think you should show us what the schema is for your users table. It honestly sounds like more of a DB design issue than a problem with ActiveRecord.

[Luke Redpath]

OK, this is all rather tiring - I've already posted a solution yet people continue to say its "not possible". Its certainly possible and just to reaffirm things, I've knocked up quick Rails test app and with one small tweak to my originally posted code, it works. I see no reason why there cannot be a many-to-many relationship between objects of a single class, or in this case, records of a single table. I do not know if many-to-many is the correct term in this case - I know a one to * relationship within a single table is known as a recursive or reflexive relationship - I don't know if this would be called the same, but it makes sense either way.

You have a users table:

CREATE TABLE users (
  id INT,
  name VARCHAR(20),
  PRIMARY KEY(id)
);

A join table, eg:

CREATE TABLE users_known_users (
  user_id INT,
  known_user_id INT,
  PRIMARY KEY(user_id, known_user_id)
);

Now, the only change from my original code - you cannot specify the class name as "known_users" as there is no such model - just a user model. However, having code such as:

luke = User.find(1)
user.users

Isn't very clear, so you just create an alias for the users() method called known_users(). The full model:

class User < ActiveRecord::Base
  has_and_belongs_to_many  :users,
                           :join_table => 'users_known_users',
                           :foreign_key => 'user_id',
                           :association_foreign_key => 'known_user_id'

  def known_users
    self.users
  end
end

And the following works:

luke = User.create(:name => 'Luke')
#=> #<User:0xb73d60b0 @attributes={"name"=>"Luke", "id"=>"1"}>
luke.known_users.length
#=> 0
luke.known_users.create(:name => 'Dave')
#=> #<User:0xb73be398 @new_record_before_save=true, @attributes={"name"=>"Dave", "id"=>2}, @new_record=false, @errors=#<ActiveRecord::Errors:0xb73bd858 @base=#<User:0xb73be398 ...>, @errors={}>>
luke.known_users.length
#=> 1

Everybody happy now?

[Luke Redpath]

OK, I've done a bit of further testing, and discovered a slight flaw with the above, which is...if you connect Person A to Person B, Person B should be connected to Person A as well. This works with a traditional many-to-many because each foreign key column in the join table points to a different table. However, in this case it doesn't, so it becomes neccesary to create two rows in the join table for each many-to-many join -- not ideal, but not a major hassle either. Its easily accomplished using two one-liner association callbacks. The updated model is now:

class User < ActiveRecord::Base
  has_and_belongs_to_many  :users,
                           :join_table => 'users_known_users',
                           :foreign_key => 'known_user_id',
                           :association_foreign_key => 'user_id',
                           :after_add => :create_reverse_association,
                           :after_remove => :remove_reverse_association

  def known_users
    self.users
  end

  private
    def create_reverse_association(associated_user)
      associated_user.known_users << self unless associated_user.known_users.include?(self)
    end

    def remove_reverse_association(associated_user)
      associated_user.known_users.delete(self) if associated_user.known_users.include?(self)
    end
end

Note the checks to see if a relationship already exists inside the callbacks - these are nessecary otherwise you would end up with an infinite loop (as each association callback is creating another association, this in turn would call another association callback to the original user and so on...).

[samsm]

Hey, like I said, I'm happy! I wish I could find what I was doing that didn't work previously, I could have sworn it was exactly this, but obviously not.

A little note for anyone watching, (this confused me for a second), known_users.create does add a user to the database but it doesn't seem to commit the relationship to the database. Looks to me like you have to:

>> luke.known_users.create(:name => 'Dave').save

or

>> dave = User.create(:name => 'Dave')
>> luke.known_users << dave

Or is there a better way to do it?

[Luke Redpath]

It seems that is the expected behaviour. You could also bypass the temp variable:

luke.known_users << User.create(:name => 'Dave')

[samsm]

Originally Posted by Luke Redpath

It seems that is the expected behaviour. You could also bypass the temp variable:

luke.known_users << User.create(:name => 'Dave')

Good point! Thank you for looking into all this, by the way.

[Luke Redpath]

Np

[dilbo]

Luke's method works perfectly, thanks everyone for your great help

[Dr Livingston]

Umm...

Seams to me, that what you've got to do - basically - is fart about all day, just to get this thing to work with the ActiveRecord?

I'm sure there would - proberly - be a better approach to this no?

[Luke Redpath]

What are you talking about? ActiveRecord works out of the box for no associations and with one or two lines of code for the common associations, and one or two lines of code for single table inheritance. And it took me all of an hour to work out how do do many to many relationships within the same table (hardly a common requirement).

ActiveRecord can also be set up to work with hierarchial structures using one or two lines of code.

You hardly have to "fart about all day". What are you babbling on about? Do you even have a point, or something worthwhile to contribute to this forum? You may contribute to the PHP forum but it seems that all you do in here is ramble on about nothing you know little to nothing about. Why do you even bother?

[jpease]

Originally Posted by Luke Redpath

You may contribute to the PHP forum but it seems that all you do in here is ramble on about nothing you know little to nothing about. Why do you even bother?

How else do you get to 4,000+ posts?

BTW - Luke, didn't mean to imply by my earlier post that your solution wouldn't work. I'm still learning RoR, and in the original post it seemed as though the poster was going about things the wrong way. I guess as it turns out Rails uses slightly different terminology then I am used to.

In the past I would have called the table users_known_users a "Linking Table" or a "Lookup Table". When I hear "Join Table" I think of a table created by executing a SQL JOIN statement, but I guess in RoR a "Join Table" == "Linking Table".

Or maybe I have everything wrong.

[Luke Redpath]

Originally Posted by jpease

How else do you get to 4,000+ posts?

BTW - Luke, didn't mean to imply by my earlier post that your solution wouldn't work. I'm still learning RoR, and in the original post it seemed as though the poster was going about things the wrong way. I guess as it turns out Rails uses slightly different terminology then I am used to.

In the past I would have called the table users_known_users a "Linking Table" or a "Lookup Table". When I hear "Join Table" I think of a table created by executing a SQL JOIN statement, but I guess in RoR a "Join Table" == "Linking Table".

Or maybe I have everything wrong.

No you are right, what I referred to as a "join table" is simply a table that links two tables in a many-to-many relationship, like you said.

[Dr Livingston]

How else do you get to 4,000+ posts?

What am I babbling on about? Well, despite the manner in which I posted, what I was actually asking, is that is there not another way that you could tackle this, bar using the Active Record?

I would have thought that was a bit obvious really, if you took the time not to read too much in the part about farting about all day

There are alternatives, I'm just asking if they are appropriate that's all. As to me not knowing much about Ruby, well that is why I visit the forum in the first place, to learn more about it.

Maybe I'll just not bother the next time, if that's your attitude huh?

[jpease]

Originally Posted by Dr Livingston

Maybe I'll just not bother the next time, if that's your attitude huh?

I don't think anybody is saying "don't come around here anymore". But I think you have to admit the manner of your posts (at least the ones I have seen) in the Ruby forum have had a somewhat inflamatory tone.

If you are genuinely interested in learning more about Ruby / Rails (and not just trying to start fights) then something along the lines of :

Originally Posted by What I Say I Meant was...

Pardon me my fellow programmers...

Using ActiveRecord to solve this problem seems to be more complicated then I would think it needs to be.

Might there be a better solution?

As opposed to :

Originally Posted by What I Actually Said was...

Umm...

Seams to me, that what you've got to do - basically - is fart about all day, just to get this thing to work with the ActiveRecord?

I'm sure there would - proberly - be a better approach to this no?

Of course at 4,000+ posts I would imagine you already know all that. So, what are we to conclude, except that you are just posting to cause trouble?

[samsm]

Originally Posted by Dr Livingston

Seams to me, that what you've got to do - basically - is fart about all day, just to get this thing to work with the ActiveRecord?

I'm sure there would - proberly - be a better approach to this no?

It's actually super easy. I can't believe that I didn't have this working when I did this last time. Simply careless on my part.

You just chose habtm, enter the custom database details because you aren't doing the usual tableA to tableB thing and you are good to go. Really, it's like 2 lines of code to get the model working.

[pratiknaik]

Originally Posted by Luke Redpath

Now, the only change from my original code - you cannot specify the class name as "known_users" as there is no such model - just a user model.

Just adding class_name => 'User' will work !

class User < ActiveRecord::Base
  has_and_belongs_to_many  :friends,
                           :join_table => 'users_known_users',
                           :foreign_key => 'user_id',
                           :association_foreign_key => 'known_user_id',
                           :class_name => 'User'
end

-Pratik

[Denyzine]

I've gotten the user example above working. I've been trying to add a third field into the user_known_user table. I'm calling it "introduced_by_id". It is essentially stating which user introduced the two users together. I've added another has_and_belongs to many relationship called "introduced_by" with a similar layout to the known_users example. Whenever I try to call it however I always seem to retrieve the 2nd row in the table and not the third. Does anyone why this is happening?

Thanks!