The SitePoint Forums have moved.

You can now find them here.
This forum is now closed to new posts, but you can browse existing content.
You can find out more information about the move and how to open a new account (if necessary) here.
If you get stuck you can get support by emailing forums@sitepoint.com

If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

one table, many-to-many

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?

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.

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.

Using your unpaid time to add free content to SitePoint Pty Ltd's portfolio?

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.

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.

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:

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...).

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:

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?

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".

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.

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?

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?