Yii Framework Forum: Please explain how to do Many-to-many relationships for newcomer - Yii Framework Forum

Jump to content

  • (2 Pages)
  • +
  • 1
  • 2
  • You cannot start a new topic
  • You cannot reply to this topic

Please explain how to do Many-to-many relationships for newcomer Rate Topic: ***** 4 Votes

#1 User is offline   NicholasA 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 1
  • Joined: 25-October 10

Posted 25 October 2010 - 07:00 AM

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

#2 User is offline   macinville 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 268
  • Joined: 25-October 10
  • Location:Marikina City, Philippines

Posted 25 October 2010 - 02:14 PM

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/you...ndex.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 ;)).
Tell your tail.
Don't be shy to upvote a post if it was able to help you
0

#3 User is offline   jefftulsa 

  • Advanced Member
  • Yii
  • Group: Yii Dev Team
  • Posts: 168
  • Joined: 06-October 08
  • Location:Austin, TX

Posted 26 October 2010 - 12:16 AM

*
POPULAR

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

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


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

#4 User is offline   Nitish Bezzala 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 4
  • Joined: 12-August 10

Posted 16 November 2010 - 09:02 AM

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.

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
;


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

#5 User is offline   fouss 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 394
  • Joined: 05-October 10
  • Location:Bamako Mali

Posted 15 December 2010 - 02:40 AM

thanks Jeffulsa for your example. it works find

How to display this actionTesteManyToMany in a view like CgridView ?
Posted Image
1

#6 User is offline   Shalabh Vyas 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 3
  • Joined: 07-January 11

Posted 20 January 2011 - 05:05 AM

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

#7 User is offline   Ohawk 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 14
  • Joined: 27-January 11

Posted 08 February 2011 - 03:42 PM

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

#8 User is offline   digitalzombie 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 17
  • Joined: 19-August 10

Posted 16 February 2011 - 01:04 AM

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

http://stackoverflow...ationship-table

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

#9 User is offline   jmariani 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 53
  • Joined: 12-January 12

Posted 17 January 2012 - 10:58 AM

View Postdigitalzombie, on 16 February 2011 - 01:04 AM, said:

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

#10 User is offline   lifeLogic 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 6
  • Joined: 22-March 12

Posted 23 March 2012 - 03:20 AM

@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.yiiframew...n/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!
0

#11 User is offline   FlannelTuba 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 2
  • Joined: 01-November 12

Posted 20 November 2012 - 10:47 AM

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
0

#12 User is offline   mindplay 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 397
  • Joined: 03-September 09
  • Location:New York

Posted 03 December 2012 - 08:40 AM

jefftulsa's example desperately needs to be included in the guide/manual.
1

#13 User is offline   mindplay 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 397
  • Joined: 03-September 09
  • Location:New York

Posted 03 December 2012 - 09:23 AM

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

#14 User is offline   1rhino 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 1
  • Joined: 20-January 13

Posted 26 January 2013 - 11:56 PM

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

#15 User is offline   mindplay 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 397
  • Joined: 03-September 09
  • Location:New York

Posted 28 January 2013 - 09:39 AM

View Post1rhino, on 26 January 2013 - 11:56 PM, said:

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

#16 User is offline   Sriyan 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 2
  • Joined: 10-March 13

Posted 13 March 2013 - 06:02 AM

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

#17 User is offline   Sriyan 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 2
  • Joined: 10-March 13

Posted 13 March 2013 - 11:50 AM

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.

Attached File(s)


2

#18 User is offline   sldra 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 123
  • Joined: 11-October 12

Posted 29 April 2013 - 08:40 PM

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

#19 User is offline   chamara 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 47
  • Joined: 05-June 13
  • Location:srilanka

Posted 14 June 2013 - 03:02 AM

View Postjefftulsa, on 26 October 2010 - 12:16 AM, said:

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

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


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.

can u show me this throught join query

using cdbcritaria
0

#20 User is offline   veontomo 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 27
  • Joined: 19-October 12

Posted 09 September 2013 - 06:47 AM

View Postjefftulsa, on 26 October 2010 - 12:16 AM, said:

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.
....................................
-Hope this was helpful.


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?
The more one gets to know of Yii, the more one values Yii.
0

Share this topic:


  • (2 Pages)
  • +
  • 1
  • 2
  • 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