MANY_MANY relation with same model

In order to implement some sort of ‘friendship’ relation between two users, I wanted to create a MANY_MANY relation from my user model to itself. Much to my surprise, the following actually works (well, kind of):

User.php




public function relations() {

	return array(

		'related' => array(

			self::MANY_MANY, 'User', 'Relation(user1, user2)',

		),

	);

}



However, this property only returns the related users for which the current user is in the first column, and the related user is in the second column. I want this to be a symmetrical relationship, so that relations where the current user is in the second column are included as well. I can think of two ways to do this, but I don’t like either of them:

  • Inserting a relation twice into the relation table (user1, user2) and (user2, user1)

  • Creating another relation with swapped keys like so:




public function relations() {

	return array(

		'related' => array(

			self::MANY_MANY, 'User', 'Relation(user1, user2)',

		),

		'related2' => array(

			self::MANY_MANY, 'User', 'Relation(user2, user1)',

		),


	);

}



Is there a better way to make this relationship symmetrical without inserting duplicate data or having to access two separate model relations?

Maybe I am not understanding you correctly, but I don’t think you need to do that.

If there’s a friendship defined between A and B, then you only need to know once, no need to get the friendship from B to A.

That’s exactly what I’m trying to do, but when I look for friends of A for example, in this table:




+=======+=======+

| user1 | user2 |

+=======+=======+

|   A   |   B   |

+-------+-------+

|   C   |   A   |

+-------+-------+



In one friendship, A is listed in the first column (user1), and in another friendship he may be listed in the second column (user2). I’m looking for a way to find all friendships involving A in a single AR relation (so in this case that relation should return an array containing both B and C).

Sorry, I didn’t get that you wanted to store the relationship in the same table.

I think it would be better design to have a some kind of friendships table, that defines the friendships between User and User. You will not have this problem anymore…

I think I’m still not getting the problem across very clearly :(

The table I sketched IS a friendship table. It just consists of two columns, and each cell contains an ID corresponding to my User table.

In the above table, User A is friends with User B and User C. Because both columns in the friendship table contain a User ID, I need to search both columns to find all friendships involving User A because his user ID may sometimes be in the first column, and sometimes be in the second column. For both of these situations, I need to get the user ID of the friend in the other column.

That was what I was trying to make clear with the table I sketched. I really don’t know how to make this any clearer :blink:

Just a quick idea: How about storing each relationship between A-B twice? (Same for deleting of course…)


+=======+=======+

| user1 | user2 |

+=======+=======+

|   A   |   B   |

+-------+-------+

|   B   |   A   |

+-------+-------+

Yeah, I thought of that too, but it would make an already big table twice as big, so I was kind of hoping to avoid that…

The more i think about it, i feel like it’s the way to go. A “friendship” always has two sides: a “source” and a “destionation”. (A could see B as a friend, but B might not like A ;) ). So i wouldn’t worry about table space. If DB space becomes a problem you still can look for a workaround on the DB layer. It also shouldn’t slow down things, as long as you create correct indices on your table.

Heh, It’s not necessarily disk or db space I worry about, more that I’ve always been taught not to fill my tables with redundant data because if you need to do that, it usually indicates a problem with your DB design ;)

How about SQL’s UNION operator? I think it would suit my needs here because I can combine two result sets into one table with it, something like this:

[sql]

SELECT user1 as first, user2 as second FROM friendship WHERE first = A

UNION

SELECT user1 as second, user2 as first FROM friendship WHERE first = A

[/sql]

However I haven’t found a way yet to implement it in an AR relation.

Thanks, finally i learn the UNION operator ;). Couldn’t you create a DB view from your statement and use that for AR relation definition?

Hmm yes, that could work :) I never did anything with views before so I’ll go looking for some docs and try it out, thanks!

OK I’ve been trying this approach but I’m having some trouble with it.

I created the following view in my (SQLite) DB:

[sql]

CREATE VIEW "Symmetric_Friendship" AS

SELECT user1 AS me, user2 AS other FROM friendship

UNION

SELECT user2 AS me, user1 AS other FROM friendship

[/sql]

I created friendship model and changed its tableName() method to return ‘Symmetric_Friendship’. I also added a friendship relation to my user model as follows:




public function relations() {

	return array(

		'friends' => array(

			self::HAS_MANY, 'Friendship', 'me',

		),

	);

}



However, when a friendship model gets initialized, I’m getting a Yii Error page:

Querying the database actually works fine, but it seems Yii is having some problems because there is no primary key defined on the table. Unfortunately, I can’t set a PK on a view so I need to solve this in the model. I tried overriding afterConstruct() to set a composite PK like this:




protected function afterConstruct() {

	$this->primaryKey = array(

		'me' => $this->me, 

		'other' => $this->other,

	);

}



But it needs to be defined earlier I guess, since this doesn’t solve my problem. I’m having some problems with overriding __construct(), If I do that I get either no output at all or a memory exhaustion.

Any ideas of how I could get this to work?

Another thing that struck me this morning is that even if this starts working, I’m going to have some problems saving friendship data, as I can’t save to the view, only to the underlying table…

instead of a relation, what about this?




public function getFriends()

{

    $sql = 'SELECT * FROM

(

  SELECT request_user_id AS friend_id FROM friends WHERE response_user_id = 589 AND STATUS = 1

    UNION

  SELECT repsonse_user_id AS friend_id FROM friends WHERE request_user_id = 589 AND STATUS = 1

) AS buddies

 

INNER JOIN users AS u ON u.id = buddies.friend_id WHERE is_active = 1';

    

    return User::model()->findAllBySQL($sql);

}



Yeah that’s possible but I’d really like to keep the AR powers of Yii, mostly because this will in time become a collaborative project and DB independence is also one of its requirements, so I’d like to keep the number of hacks to a minimum ;)

Right now I’m leaning back towards dropping the view and just inserting friendship data twice into the same table, it’ll probably save me a lot of hassle…

Do you think this query could be translated into criteria that would work for your purposes? In a while I’ll be dealing with a similar problem, and I had this syntax suggested when I brought up the concept over at devshed:


SELECT IF(target=user1,user1,user2) AS user1, IF(target=user1,user2,user1) AS user2 FROM Friendship 

	WHERE target=user1 OR target=user2;

I haven’t checked how this would affect AR powers yet, but the query itself works well with mysql 5.0.x.

Having the exact same issue. Could someone experienced like Qiang look into this?

Sorry for the big necroposting, but I was searching for clues on how to implement the same type of functionality ("friendship" relationships) with YII and this is the best result.

I did implement it following the View suggestion by Sander on post #12, and I think it’s the optimal solution, but I don’t really get what he wanted to do next, so I wanted to share what I did and how it works.

Basically, I don’t get why you would need a model referencing the view.

Usually, when I need to query the "Friendship" model, I am interested in its full details, so I need to know who requested the friendship, whether it was accepted and ignored etc…

Then I wouldn always query the table, not the view.

I only want to query the view when I need to know which users a given user is friend with, and thus I need the UNION of both user1-to-user2 and user2-to-user1 requests.

But when I’m doing this, in my controller I only use user models through their defined relations, I don’t need to load a friendship model for that.

So, the only thing I changed is the relation in my user model, which was defined in this way:




public function relations() {

        return array(

                'related' => array(

                        self::MANY_MANY, 'User', 'Friendship(user1, user2)',

                ),

        );

}



and now references the view, so (using the field names defined by Sander) should be:




public function relations() {

        return array(

                'related' => array(

                        self::MANY_MANY, 'User', 'Symmetric_Friendship(me, other)',

                ),

        );

}



This solution works for me, I hope it can help other people who maybe will bump in this thread.