Doctrine 1
  1. Doctrine 1
  2. DC-302

Issues when using automatic relations ordering through 'orderBy' param in m2m relations

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 1.2.0
    • Fix Version/s: 1.2.1
    • Component/s: Record, Relations
    • Labels:
      None
    • Environment:
      php 5.3/win, doctrine 1.2 svn, ATTR_QUOTE_IDENTIFIER = true, ATTR_USE_DQL_CALLBACKS = true

      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.

        Activity

        Hide
        Maciej Hołyszko added a comment -

        Attached test case. I did not know how to test the second issue - is there a db profiler available during unit testing?

        Show
        Maciej Hołyszko added a comment - Attached test case. I did not know how to test the second issue - is there a db profiler available during unit testing?
        Hide
        Jonathan H. Wage added a comment -

        Thanks for the report and test case. I made a change and your test case passes now.

        Show
        Jonathan H. Wage added a comment - Thanks for the report and test case. I made a change and your test case passes now.

          People

          • Assignee:
            Jonathan H. Wage
            Reporter:
            Maciej Hołyszko
          • Votes:
            2 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: