Please explain how to do Many-to-many relationships for newcomer

I have just started learning yii today. So far I am very excited!

However I have spent many fruitless hours trying to work out how to setup many-to-many relationships. I know I am not the only one. I just want to start with CRUD at present.

My research seems to indicate that using gii-template-collection, which provides the CAdvancedArBehavior and the Relation widget, is going to give me the best experience.

However I cannot find documentation suitable for a newcomer.

Searching in the forums just gives me endless discussions of various hacks and apparent issues.

I have tried to reproduce what the cadvancedarbehavior documentation seems to be explaining, but it isnā€™t happening.

Could someebody please

  1. Summarise the current support for many-to-many relationships with yii. Is better inbuilt support (like in Rails) on the Roadmap?

  2. Provide a quick tutorial as to how to do this (hopefully with gii-template-collection if that is the best user experience).

i.e.

a) MYSQL Schema of three sample tables and the exact foreign key relationships between them

B) Which FullModels and FullCRUDS I need to generate, with what settings.

c) Any limitations, known bugs or gotchas.

It would be much appreciated!

Edit the main.php located in your /your-web-application-name/protected/config directory and uncomment the gii portion under module array (be sure to type in there your desired password in the appropriate index). Then access gii with http://localhost/your-web-application-name/index.php?r=gii. At the navigation panel on the left, you will see various generators you will need. ;)

I would really like to expand my explanation, but I think it would be best if I leave it to the Yii experts since itā€™s only almost a day since I studied this framework (yeah, almost no rights at all to lecture someone ;)).

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.

1 Like

Thanks jefftulsa, that was very useful, but now I have another question.

In the example above, supposing a project_user_assignment has its own attributes like a start date and an end date.

[sql]CREATE TABLE IF NOT EXISTS project_user_assignment

(

project_id Int(11) NOT NULL,

user_id Int(11) NOT NULL,

ā€˜start_dateā€™ Timestamp,

ā€˜end_dateā€™ Timestamp,

PRIMARY KEY (project_id,user_id)

) ENGINE = InnoDB

;[/sql]

How do I save these attributes when I create an assignment?

How do I access these attributes if I have either a project or a user?

thanks Jeffulsa for your example. it works find

How to display this actionTesteManyToMany in a view like CgridView ?

[size=2]The example you gave above has database entries for the MANY-MANY table already present. Could you post the code do this in code? Like, create a project and two users.Add the users to project and then do a bi-directional access from project to its users and each user to its projects. Could you post such code please?[/size]

@jefftulsa Tried your mini tutorial - has been the only way Iā€™ve got a many-to-many relationship to work using Yii. Can you tell me, rather than echoing the info how I would get it into a DataProvider for use with say the CListView widget?

Regards,

O

ā€œSo, my best guess is that if the thing in the middle of your relationship needs more columns than just the two foreign keys then you donā€™t really have a many to many relationship. You have two many to one relationships and you just havenā€™t identified the thing in the middle yet.ā€

To summarize the solution for you,

Your database design is flawed. The Junction/Associative table should only have the primary keys of the two table itā€™s trying to connect/bridge. Pure many-to-many tables should not have unique fields in their Junction/Associative table, it is most likely you have 2 of one-to-many relationships.

I have this problem too, Iā€™ve just googled it, and I believe this is the correct solution.

Very helpful comment. Thank you.

@jefftulsa Thank you for putting up this explanation.

I really appreciate it. I am new to the Yii framework and

have been struggling to get Yiiā€™s AR functionality working.

Your explanation helped me a lot!

I have read through Agile Web Application Development with Yii 1.1 and PHP5

and now working through "The Definitive Guide" and I think Yii is extremely powerful.

I suggest Yii put a link to this explanation

in the Relational Active Record section of the Definitive Guide to Yii.

http://www.yiiframework.com/doc/guide/1.1/en/database.arr. Or, better yet, put,

this explanation or a modified version of it in the Guide itself. This would save your new

users 100s of google searches and help people get into Yii faster.

The definitive guide does not give an explanation or real world example

of how to access MANY_MANY query results. I worked through the tutorial

and was trying to figure out how to access AR relational MANY_MANY query results.

Your post was the only thing I found worth using after 2 days of google searches.

Thank you for sharing!

My only regret is that it took me two (or was it three) full days of messing about with half baked examples and self tortured code flailing before I found your clear, concise and understandable example. My grateful thanks.

Now onto how to do an insert across a mant to many relation. (e.g. add new ā€˜Userā€™ to a ā€˜Projectā€™ on the Project _formā€¦)

Yikes.

  • Scott

jefftulsaā€™s example desperately needs to be included in the guide/manual.

By the way, on a note of personal preference - I do use link-tables (many-to-many), but I strongly recommend scrutinizing that decision every time.

Itā€™s a little more work up-front to add another entity (e.g. ProjectUserAssignment) than it is to just configure it as a link-table, and it does add some complexity to the code that consumes the model.

But if you think thereā€™s any chance that the relationship itself is going to need any kind of meta-data (in your case start_date and end_date) that describes the relationship, at any point in the future, itā€™s a much ā€œsaferā€ approach to add another entity - because you wonā€™t have to gut lots of consumer code later.

Thatā€™s why personally, I tend to avoid link-tables, unless Iā€™m absolutely positively 100% sure that the relationship is fully described by just the two foreign keys.

(keep in mind that many-to-many relationships are an ā€œideaā€ in our minds - itā€™s not something that really exists at the relational level, where everything is foreign keys and tables. The foreign keys in a link-table are no different from the foreign keys in any other table - you can call it a link-table and use a naming-convention or comment to indicate itā€™s purpose, but at the relational level, itā€™s a table just like any other table.)

When you create relation. You donā€™t need create foreign key for tables on database. Only declare on model class relations. AR still work fine.

Iā€™m not referring to columns with referential constraints on them - just foreign keys in general.

Hi all, I found this discussion very help full. Great work @jefftulsa.

And as many people asked, can someone please advise, how to insert data across many to many relations in yii? Here we learnt how to access data. But it would be great if someone can explain how to insert data to the database.

Thank you.

Hi,

Finally I could find a way to insert data into the database through many to many relations. Here Iā€™m considering a small example. There are three tables in my database (Student, Course and StudentCourseAssignments). Iā€™m pasting my code and if you have any easier ways to do this, please let us know.

In my Student model,





<?php


class Student extends CActiveRecord

{

	 

	public $courseNames; // I used this variable to get inputs from the view in text. I'm taking this input as coma separated course names.

	 

	public static function model($className=__CLASS__)

	{

		return parent::model($className);

	}


	public function tableName()

	{

		return 'student';

	}


	public function rules()

	{

		return array(

			array('age', 'numerical', 'integerOnly'=>true),

			array('name', 'length', 'max'=>45),

			array('courseNames','match', 'pattern'=>'/^[\w\s,]+$/', 'message'=>'Course name should be a string'),

			// The following rule is used by search().

			// Please remove those attributes that should not be searched.

			array('id, name, age', 'safe', 'on'=>'search'),

		);

	}

	

	public function afterSave(){

		parent::afterSave();

		Course::updateCourses($this->courseNames, $this->id);

	}

	

	public function relations()

	{

		return array(

			'courses' => array(self::MANY_MANY, 'Course', 'coursestudentassignment(studentId, courseId)'),

		);

	}


	public function attributeLabels()

	{

		return array(

			'id' => 'ID',

			'name' => 'Name',

			'age' => 'Age',

			'courseNames' => 'Courses',

		);

	}


	public function search()

	{


		$criteria=new CDbCriteria;


		$criteria->compare('id',$this->id);

		$criteria->compare('name',$this->name,true);

		$criteria->compare('age',$this->age);


		return new CActiveDataProvider($this, array(

			'criteria'=>$criteria,

		));

	}

}



In my Course model,




<?php


class Course extends CActiveRecord

{

	public static function model($className=__CLASS__)

	{

		return parent::model($className);

	}

	

	public static function isExistCourse($courseName){

		$course = self::model()->find('LOWER(name)=?', array($courseName));

		return !($course===null);	

	}

	

	public static function updateCourses($coursesString, $studentId){

		$explodedCourses = explode(",", $coursesString);

		foreach($explodedCourses as $singleCouse){

			$singleCouse = trim($singleCouse);

			if($singleCouse==="")

				continue;

			

			if(!(Course::isExistCourse(strtolower($singleCouse)))){

				$course = new Course;

				$course->attributes = array('name'=>$singleCouse);

				if($course->save()){

					Coursestudentassignment::updateAssignments($studentId, $course->id);

				} else

					continue;

				

			}	

		}

	}


	public function tableName()

	{

		return 'course';

	}


	public function rules()

	{

		return array(

			array('duration', 'numerical'),

			array('name', 'length', 'max'=>45),

			array('id, name, duration', 'safe', 'on'=>'search'),

		);

	}


	public function relations()

	{

		return array(

			'students' => array(self::MANY_MANY, 'Student', 'coursestudentassignment(courseId, studentId)'),

		);

	}


	public function attributeLabels()

	{

		return array(

			'id' => 'ID',

			'name' => 'Name',

			'duration' => 'Duration',

		);

	}


	public function search()

	{


		$criteria=new CDbCriteria;


		$criteria->compare('id',$this->id);

		$criteria->compare('name',$this->name,true);

		$criteria->compare('duration',$this->duration);


		return new CActiveDataProvider($this, array(

			'criteria'=>$criteria,

		));

	}

}



In my Coursestudentassignment model,




<?php

class Coursestudentassignment extends CActiveRecord

{

	public static function model($className=__CLASS__)

	{

		return parent::model($className);

	}


	public static function updateAssignments($studentId, $courseId){

		$criteria=new CDbCriteria;

		$criteria->condition='studentId=:studentId and courseId=:courseId';

		$criteria->params=array(':studentId'=>$studentId, ':courseId'=>$courseId);

		

		$assignment = self::model()->find($criteria);

		if($assignment===null) {

			$assignment = new Coursestudentassignment;

			$assignment->attributes = array('studentId'=>$studentId,

											'courseId'=>$courseId);

			$assignment->save();

		}

	}

	 

	public function tableName()

	{

		return 'coursestudentassignment';

	}


	public function rules()

	{

		return array(

			array('studentId, courseId', 'required'),

			array('studentId, courseId', 'numerical', 'integerOnly'=>true),

			array('studentId, courseId', 'safe', 'on'=>'search'),

		);

	}


	public function attributeLabels()

	{

		return array(

			'studentId' => 'Student',

			'courseId' => 'Course',

		);

	}


	public function search()

	{

		$criteria=new CDbCriteria;


		$criteria->compare('studentId',$this->studentId);

		$criteria->compare('courseId',$this->courseId);


		return new CActiveDataProvider($this, array(

			'criteria'=>$criteria,

		));

	}

}



And in the _form.php in views I added a text field to collect courses names.




 .......


<div class="row">

    <?php echo $form->labelEx($model,'courseNames'); ?>

    <?php echo $form->textField($model,'courseNames'); ?>

    <?php echo $form->error($model,'courseNames'); ?>

</div>


 .......



And Iā€™m attaching my files here also. Iā€™ve tested this code and worked fine.

Thanx guys.

Thank you very much your tutorials.this is grate for bigginers to get the idea about behavior of mode view and contraller in yii.thanks again.

can u show me this throught join query

using cdbcritaria

Dear Jefftulsa, thank you for the example, itā€™s very helpful. Iā€™d like to ask what if I wanted to have a possibility to assign a user to a project, how then do I have to proceed?