[TAQTICA]

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.

[TAQTICA]

Correction:

Member relationships are:

public function relations()
        {
                return array(
                        'groups' => array(self::BELONGS_TO, 'Group', 'group_id'),
                );
        }

[got 2 doodle]

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.

[TAQTICA]

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.

[thyseus]

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

View Examples here