How to define triple entity relationship?

Archives Yii 1.0.x Discussions
1

Hi,

I am very new to Yii. I have this kind of relation:

User(id, name, …)

Project(id, name, …)

Role(id, name, …)

UserRoleProject(userid, roleid, projectid)

Underline items are primary keys. All are MANY_MANY.

The question is: "How can I define the relation in each of model User, Project, Role.

In model User, I have tried to declare:

'roles'=>array(self::MANY_MANY, 'role'=>'userprojectrole(userid,projectid,roleid)', 'alias'=>'roles', 'together'=>true),

'projects'=>array(self::MANY_MANY, 'project'=>'userprojectrole(userid,roleid,projectid)', 'alias'=>'projects', 'together'=>true),

When I called $model->with('roles', 'projects')->findAll(), from the log, I could see that table UserProjectRole was used twice in the joins. I understand with my upper declarations, I cannot expect other result. Please show me how to correct the declarations.

Below is the example of the data and result I have got,

User (1, 'UA')

Project (1, 'PA') (2, 'PB')

Role (1, 'RA') (2, 'RB')

UserRoleProject (1, 1, 1) – userid, roleid, projectid

UserRoleProject (1, 2, 2)

It returns 4 rows:

1 1 1

1 1 2

1 2 1

1 2 2

While it should have only 2 rows in UserRoleProject

Thank you.

2

There is no solution then, I guess.

I think I must use findAllBySql for now.

3

I need to improve my many-to-many experience so decided to check this out.

I guess this particular problem can probably be solved by a number of one-to-many relationships (also dependent on which table is primary in the query). I also think this kind of relationship is common in DW (cubes).

As I understand the RAR syntax, it seems like a N-table many-to-many relationship needs to be declared N-1 times (N FK's/PK-segments in association table). Unfortunately N-1 joins with the association table are generated, despite the fact that the same association table declaration is used every time. ('joinType'=>'INNER JOIN' also has to be declared.). Maybe the only thing that needs to be changed is to suppress the 2nd to Nth joins?

I also tried the FindAllBySQL() approach, but can't figure out how to access the 'roles'  and 'projects' parts of the query result.

The SQL boils down to something like this:



<?php


    $data = User::model()->findAllBySQL("


    SELECT 


      `User`.`UserId`, `User`.`Username`, 


      roles.`RoleId`, roles.`RoleName`, 


      projects.`ProjectId`, projects.`ProjectName` 


    FROM `User` 


    INNER JOIN `UserRoleProject` urp ON `User`.`UserId`=urp.`UserId` 


    INNER JOIN `Role` roles ON roles.`RoleId`=urp.`RoleId` 


    INNER JOIN `Project` projects ON projects.`ProjectId`=urp.`ProjectId`


    ");


?>

Not sure I'm completely on the right track, though. Comments welcome,

/Tommy

4

Good investigation!

Maybe we can create a UserRoleProject model and use it as the primary model to query with?

If the User model is used as primary model, the whole query has to be split into two, one joining with Role, the other joining with Project.

5

Here is my way to list all pairs of role-project of a user:



<?php


$sql = 'SELECT role.id as RoleID, role.name as RoleName, project.id as ProjectID, project.name as ProjectName


FROM user


LEFT OUTER JOIN userprojectrole ON  user.id = userprojectrole.userid


LEFT OUTER JOIN role ON role.id = userprojectrole.roleid


LEFT OUTER JOIN project ON project.id = userprojectrole.projectid


WHERE user.id='.$user->id;


$dbcommand = User::model()->getDbConnection()->createCommand($sql);


$dataReader = $dbcommand->query();


$this->render('roleproject', array('user'=>$user, 'data'=>$dataReader->readAll()));


?>