How to manage a Many to Many relationship

Hi all,

I’m trying to build an action where you can view all the groups where the current user belongs to. I would like to know the best way to do it.

The tables I have are the following:




CREATE TABLE IF NOT EXISTS `tbl_group` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `name` varchar(128) COLLATE utf8_spanish_ci NOT NULL,

  PRIMARY KEY (`id`),

  UNIQUE KEY `name` (`name`)

)


CREATE TABLE IF NOT EXISTS `tbl_member` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `group_id` int(11) NOT NULL,

  `user_id` int(11) NOT NULL,

  `is_admin` tinyint(4) NOT NULL,

  `is_creator` tinyint(4) NOT NULL,

  `join_time` int(11) NOT NULL,

  PRIMARY KEY (`id`),

  UNIQUE KEY `MEMBER_GROUP_USR` (`friendgroup_id`,`user_id`)

)



The other table is the typical tbl_user.

The Group class relations array is:




	public function relations()

	{

		return array(

			'members' => array(self::HAS_MANY, 'Member', 'group_id'),

			'memberCount' => array(self::STAT, 'Member', 'group_id'),

                );

        }



Member relationship:




	public function relations()

	{

		return array(

			'groups' => array(self::BELONGS_TO, 'FriendGroup', 'friendgroup_id'),

		);

	}



User relationship:




	public function relations()

	{

		return array(

			'memberOf' => array(self::HAS_MANY, 'Member', 'user_id'),

		);

	}




The question is how to write the code in the controller action in order to view all the groups (table tbl_group) where the user is a member. I would like to use the zii.widgets.CListView so I’ll need to pass a CActiveDataProvider. I don’t know if this is possible (in this case I don’t know how to do it) or I should do it another way.

Thanks in advance.

Correction:

Member relationships are:




public function relations()

        {

                return array(

                        'groups' => array(self::BELONGS_TO, 'Group', 'group_id'),

                );

        }



Is this a general "how to do a many many relation" or is this a Yii specific question.

normally you need three tables (I don’t know of another way, but I am willing to learn)

User table

id

name

Group table

id

name

UserGroup table

id

user_id

group_id

So the id’s are stored in the UserGroup table each pointing to the other tables. So now you can have many users belonging to many groups.

I don’t think Yii has anything built in to automatically manage the UserGroup table, this has to be done programatically.

Does this point you in the right direction? Or do I misunderstand your question?

doodle.

Hi the question is general, but I’m trying to base it in a specific example.

A more specific question would be how to used many to many relationships to build data sets of a DataProvieder class.

I found that I can use the user to group relationship to get the group ids of the ones to the user belongs to, and use this to create a condition to query on groups (using a standard criteria for the DataProvider object).

My question would be if there’s another method where this can be more efficient. I were writing just SQL code, I would do a join between Group and UserGroup tables reading only group attributes (what I need is all the information of the groups where a user belongs to). I don’t know if there is a way I can configure a criteria to be applied over the Group class to do this in order to issue a single SQL statement to the database beside the former method that generates two.

You could try the CAdvancedArBehavior which makes the handling of MANY_MANY much easier. It contains documentation and example Code.

View Examples here