Yii Framework Forum: MANY_MANY relation with same model - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

MANY_MANY relation with same model Creating a symmetrical AR relation Rate Topic: -----

#1 User is offline   Sander 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 169
  • Joined: 03-November 09
  • Location:Amsterdam, Netherlands

Posted 25 November 2009 - 10:42 AM

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?
0

#2 User is offline   basva 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 23
  • Joined: 19-August 09

Posted 25 November 2009 - 04:25 PM

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

#3 User is offline   Sander 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 169
  • Joined: 03-November 09
  • Location:Amsterdam, Netherlands

Posted 25 November 2009 - 04:36 PM

View Postbasva, on 25 November 2009 - 04:25 PM, said:

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

#4 User is offline   basva 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 23
  • Joined: 19-August 09

Posted 26 November 2009 - 01:03 PM

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

#5 User is offline   Sander 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 169
  • Joined: 03-November 09
  • Location:Amsterdam, Netherlands

Posted 26 November 2009 - 05:09 PM

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:
0

#6 User is offline   Mike 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 3,016
  • Joined: 06-October 08
  • Location:Upper Palatinate

Posted 27 November 2009 - 03:21 AM

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

+=======+=======+
| user1 | user2 |
+=======+=======+
|   A   |   B   |
+-------+-------+
|   B   |   A   |
+-------+-------+

0

#7 User is offline   Sander 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 169
  • Joined: 03-November 09
  • Location:Amsterdam, Netherlands

Posted 27 November 2009 - 03:41 AM

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

#8 User is offline   Mike 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 3,016
  • Joined: 06-October 08
  • Location:Upper Palatinate

Posted 27 November 2009 - 03:49 AM

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

#9 User is offline   Sander 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 169
  • Joined: 03-November 09
  • Location:Amsterdam, Netherlands

Posted 27 November 2009 - 03:58 AM

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:

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


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

#10 User is offline   Mike 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 3,016
  • Joined: 06-October 08
  • Location:Upper Palatinate

Posted 27 November 2009 - 04:04 AM

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

#11 User is offline   Sander 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 169
  • Joined: 03-November 09
  • Location:Amsterdam, Netherlands

Posted 27 November 2009 - 04:21 AM

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!
0

#12 User is offline   Sander 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 169
  • Joined: 03-November 09
  • Location:Amsterdam, Netherlands

Posted 01 December 2009 - 03:38 AM

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

I created the following view in my (SQLite) DB:
CREATE VIEW "Symmetric_Friendship" AS
SELECT user1 AS me, user2 AS other FROM friendship
UNION
SELECT user2 AS me, user1 AS other FROM friendship


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:

Quote

PHP Error
Description

Invalid argument supplied for foreach()
Source File

/Users/Sander/Documents/Dev/Tools/yii/framework/db/ar/CActiveFinder.php(724)
00712:     {
00713:         // determine the primary key value
00714:         if(is_string($this->_pkAlias))  // single key
00715:         {
00716:             if(isset($row[$this->_pkAlias]))
00717:                 $pk=$row[$this->_pkAlias];
00718:             else    // no matching related objects
00719:                 return null;
00720:         }
00721:         else // is_array, composite key
00722:         {
00723:             $pk=array();
00724: foreach($this->_pkAlias as $name=>$alias)
00725:             {
00726:                 if(isset($row[$alias]))
00727:                     $pk[$name]=$row[$alias];
00728:                 else    // no matching related objects
00729:                     return null;
00730:             }
00731:             $pk=serialize($pk);
00732:         }
00733: 
00734:         // retrieve or populate the record according to the primary key value
00735:         if(isset($this->records[$pk]))
00736:             $record=$this->records[$pk];


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

#13 User is offline   mbi 

  • Master Member
  • PipPipPipPip
  • Yii
  • Group: Members
  • Posts: 608
  • Joined: 08-May 09

Posted 01 December 2009 - 05:06 AM

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);
}

0

#14 User is offline   Sander 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 169
  • Joined: 03-November 09
  • Location:Amsterdam, Netherlands

Posted 01 December 2009 - 05:37 AM

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

#15 User is offline   Ivo Roper 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 36
  • Joined: 22-September 09
  • Location:Seattle, WA, USA

Posted 17 December 2009 - 12:24 PM

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.


View PostSander, on 01 December 2009 - 05:37 AM, said:

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

0

#16 User is offline   Thomas Jensen 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 150
  • Joined: 01-August 09

Posted 17 May 2011 - 03:49 PM

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

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users