Here is a quick tutorial:
Taking the example outlined in the Yii book when building the project and task management application, we’ll focus on projects and users. Users can be associated to many projects and projects can have many users.
The Schema (using MySQL):
[sql]CREATE TABLE project (
id INTEGER NOT NULL auto_increment,
name varchar(128) NOT NULL,
PRIMARY KEY (id)
) ENGINE = InnoDB
;
CREATE TABLE user
(
id INTEGER NOT NULL AUTO_INCREMENT,
username Varchar(256),
PRIMARY KEY (id)
) ENGINE = InnoDB
;
CREATE TABLE IF NOT EXISTS project_user_assignment
(
project_id Int(11) NOT NULL,
user_id Int(11) NOT NULL,
PRIMARY KEY (project_id,user_id)
) ENGINE = InnoDB
;
//Add foreign key relationships on project_user_assignment:
ALTER TABLE project_user_assignment ADD CONSTRAINT FK_project_user FOREIGN KEY (project_id) REFERENCES project (id) ON DELETE CASCADE ON UPDATE RESTRICT;
ALTER TABLE project_user_assignment ADD CONSTRAINT FK_user_project FOREIGN KEY (user_id) REFERENCES user (id) ON DELETE CASCADE ON UPDATE RESTRICT;
//Insert some "seed" data into these tables
INSERT INTO project (name) values (‘Test Project 1’), (‘Test Project 2’), (‘Test Project 3’);
INSERT INTO user (username) values (‘John’), ( ‘Jane’), (‘Joe’);
//proj 1 has 1 user, proj 2 has 2 users and proj 3 has all three users
//John is in all three projects, Jane is in projects 2 and 3 and Joe is only in project 3
INSERT INTO project_user_assignment (project_id, user_id) values (1,1), (2,1), (2,2), (3,1), (3,2), (3,3);[/sql]
With these tables in place, you can create AR model classes for project and user (no need to create an AR model class for the association table project_user_assignment)…Using Gii or by whatever means you desire.
Now you have two model classes Project and User. If you used Gii to auto-generate these classes, you will already notice the many-to-many relationship declared for you in their respective ::relations() methods (if you are creating your own AR classes, you will need to ensure these methods are in place):
User class:
public function relations()
{
return array(
'projects' => array(self::MANY_MANY, 'Project', 'project_user_assignment(user_id, project_id)'),
);
}
Project class:
public function relations()
{
return array(
'users' => array(self::MANY_MANY, 'User', 'project_user_assignment(project_id, user_id)'),
);
}
With this in place, you can now take advantage of these AR relations. If you were to create a new action method in say your SiteController class to test these out:
public function actionTestManyToMany()
{
$projects = Project::model()->findAll();
$users = User::model()->findAll();
foreach($projects as $project)
{
echo $project->name . " has " . count($project->users) . " users. They are:<br />";
foreach($project->users as $user)
{
echo $user->username . "<br />";
}
echo "<br />";
}
echo "<hr />";
foreach($users as $user)
{
echo $user->username . " is associated with " . count($user->projects) . " projects. They are:<br />";
foreach($user->projects as $project)
{
echo $project->name . "<br />";
}
echo "<br />";
}
}
And then visit this controller/action pair in your browser, by visiting the url route site/testManyToMany, you would see something like the following:
Test Project 1 has 1 users. They are:
John
Test Project 2 has 2 users. They are:
John
Jane
Test Project 3 has 3 users. They are:
John
Jane
Joe
John is associated with 3 projects. They are:
Test Project 1
Test Project 2
Test Project 3
Jane is associated with 2 projects. They are:
Test Project 2
Test Project 3
Joe is associated with 1 projects. They are:
Test Project 3
-Hope this was helpful.
