Details
Bug
Closed
Major
Description
Partially related to DC-240.
I found some additional problems, mainly with self-referenced relations.
I use ACL system with multiple inheritance, where order of inherited elements matters.
Here we have (Role <- m2m orderBy position - > Role) and (User < - m2m orderBy position -> Role):
class Role extends Doctrine_Record { public function setTableDefinition() { $this->hasColumn('name', 'string', 64); } public function setUp() { $this->hasMany('User as Users', array('local' => 'id_role', 'foreign' => 'id_user', 'refClass' => 'UserRole')); $this->hasMany('Role as Parents', array('local' => 'id_role_child', 'foreign' => 'id_role_parent', 'refClass' => 'RoleReference', 'orderBy' => 'position')); $this->hasMany('Role as Children', array('local' => 'id_role_parent', 'foreign' => 'id_role_child', 'refClass' => 'RoleReference')); } } class RoleReference extends Doctrine_Record { public function setTableDefinition() { $this->hasColumn('id_role_parent', 'integer', null, array('primary' => true)); $this->hasColumn('id_role_child', 'integer', null, array('primary' => true)); $this->hasColumn('position', 'integer', null, array('notnull' => true)); } public function setUp() { $this->hasOne('Role as Parent', array('local' => 'id_role_parent', 'foreign' => 'id', 'onDelete' => 'CASCADE')); $this->hasOne('Role as Child', array('local' => 'id_role_child', 'foreign' => 'id', 'onDelete' => 'CASCADE')); } } class User extends Doctrine_Record { public function setTableDefinition() { $this->hasColumn('username', 'string', 64, array('notnull' => true)); $this->hasColumn('password', 'string', 128, array('notnull' => true)); } public function setUp() { $this->hasMany('Role as Roles', array('local' => 'id_user', 'foreign' => 'id_role', 'refClass' => 'UserRole', 'orderBy' => 'position')); } } class UserRole extends Doctrine_Record { public function setTableDefinition() { $this->hasColumn('id_user', 'integer', null, array('primary' => true)); $this->hasColumn('id_role', 'integer', null, array('primary' => true)); $this->hasColumn('position', 'integer', null, array('notnull' => true)); } public function setUp() { $this->hasOne('User', array('local' => 'id_user', 'foreign' => 'id', 'onDelete' => 'CASCADE')); $this->hasOne('Role', array('local' => 'id_role', 'foreign' => 'id', 'onDelete' => 'CASCADE')); } }
Sample data:
$role1 = new Role(); $role1->name = 'admin'; // id: 1 $role1->save(); $role2 = new Role(); $role2->name = 'publisher'; // id: 2 $role2->save(); $role3 = new Role(); $role3->name = 'reviewer'; // id: 3 $role3->save(); $role4 = new Role(); $role4->name = 'mod'; // id: 4 $role4->save(); // reviewer inherits from admin, mod, publisher - in that order $role3->Parents[] = $role1; $role3->Parents[] = $role4; $role3->Parents[] = $role2; $role3->save(); // update positions $query = Doctrine_Query::create() ->update('RoleReference') ->set('position', '?', 0) ->where('id_role_child = ?', 3) ->andWhere('id_role_parent = ?', 1) ->execute(); $query = Doctrine_Query::create() ->update('RoleReference') ->set('position', '?', 1) ->where('id_role_child = ?', 3) ->andWhere('id_role_parent = ?', 4) ->execute(); $query = Doctrine_Query::create() ->update('RoleReference') ->set('position', '?', 2) ->where('id_role_child = ?', 3) ->andWhere('id_role_parent = ?', 2) ->execute(); // add test user $user = new User(); $user->username = 'test'; $user->password = 'test'; $user->fromArray(array('Roles' => array(4, 2))); $user->save(); // update positions $query = Doctrine_Query::create() ->update('UserRole') ->set('position', '?', 0) ->where('id_user = ?', 1) ->andWhere('id_role = ?', 4) ->execute(); $query = Doctrine_Query::create() ->update('UserRole') ->set('position', '?', 1) ->where('id_user = ?', 1) ->andWhere('id_role = ?', 2) ->execute();
Now, lazy-loading self-referenced m2m relations seems to be the issue (I know lazy-loading is wrong but it's needed to be like that in some parts of our system):
$role = Doctrine::getTable('Role')->find(3);
print_r($role->Parents->toArray());
The query which is created and executed during lazy-load of Parents relations is as follows:
SELECT role.id AS role__id, role.name AS role__name, role_reference.id_role_parent AS role_reference__id_role_parent, role_reference.id_role_child AS role_reference__id_role_child, role_reference.position AS role_reference__position FROM role INNER JOIN role_reference ON role.id = role_reference.id_role_parent WHERE role.id IN (SELECT id_role_parent FROM role_reference WHERE id_role_child = ?) ORDER BY role.id ASC, position
(seems a little strange as there are no automatically generated aliases e.g. r1, r2 etc. but whole table names)
The result is ordered by role.id first, then by position (without any alias and that could be an additional problem in some cases)
The result is: (as you can see the order of roles is 1, 2, 4 (positions: 0, 2, 1) instead of 1, 4, 2)
Array
(
[0] => Array
(
[id] => 1
[name] => admin
[RoleReference] => Array
(
[0] => Array
(
[id_role_parent] => 1
[id_role_child] => 3
[position] => 0
[Parent] =>
)
)
)
[1] => Array
(
[id] => 2
[name] => publisher
[RoleReference] => Array
(
[0] => Array
(
[id_role_parent] => 2
[id_role_child] => 3
[position] => 2
[Parent] =>
)
)
)
[2] => Array
(
[id] => 4
[name] => mod
[RoleReference] => Array
(
[0] => Array
(
[id_role_parent] => 4
[id_role_child] => 3
[position] => 1
[Parent] =>
)
)
)
)
It is NOT an issue with lazy-loading m2m relations between two different models:
$user = Doctrine::getTable('User')->find(1);
print_r($user->Roles->toArray());
The query generated seems to be correct: (well except the lack of an alias in front of position column in ORDER BY clause)
SELECT `r`.`id` AS `r__id`, `r`.`name` AS `r__name`, `u`.`id_user` AS `u__id_user`, `u`.`id_role` AS `u__id_role`, `u`.`position` AS `u__position` FROM `role` `r` LEFT JOIN `user_role` `u` ON `r`.`id` = `u`.`id_role` WHERE (`u`.`id_user` IN (?)) ORDER BY position
It works well for self-referenced relations where relation are defined in DQL e.g.:
$query = Doctrine_Query::create()
->from('Role r')
->leftJoin('r.Parents rp')
->orderBy('r.name ASC')
->where('r.id = ?', 3);
var_dump($query->getSqlQuery());
$result = $query->fetchOne();
print_r($result->Parents->toArray());
To sum up:
1. orderBy in m2m self-referenced relations does not work when they are lazy-loaded
2. lack of table alias for orderBy column when lazy-loading m2m relations between separate models (possibly not an issue?)
I am not sure if the first one could be fixed at all, due to specific query construction? If not, I would be glad to see a possible workaround for this problem.
Thanks in advance.
Attached test case. I did not know how to test the second issue - is there a db profiler available during unit testing?