Doctrine 1
  1. Doctrine 1
  2. DC-240

Automatic ordering through orderBy in many-to-many relation definition issue

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Critical Critical
    • Resolution: Fixed
    • Affects Version/s: 1.2.0-BETA3
    • Fix Version/s: 1.2.0-RC1
    • Component/s: Record, Relations
    • Labels:
      None
    • Environment:
      1.2 svn

      Description

      I would like to utilize new nice feature in Doctrine 1.2, namely automatic ordering of relations using orderBy property in relation definition. However I see small flaw with m2m relations:

      class User extends Doctrine_Record
      {
      	public function setTableDefinition()
      	{
      		$this->setTableName('core_users');
      		
      		$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'));
      		$this->actAs('SoftDelete');
      	}
      }
      
      class Role extends Doctrine_Record
      {
      	public function setTableDefinition()
      	{
      		$this->setTableName('core_roles');
      		
      		$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 UserRole extends Doctrine_Record
      {
      	public function setTableDefinition()
      	{
      		$this->setTableName('core_users_roles');
      		
      		$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'));
      	}
      }
      
      class RoleReference extends Doctrine_Record
      {
      	public function setTableDefinition()
      	{
      		$this->setTableName('core_roles_reference');
      		
      		$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'));
      	}
      }
      
      $query = Doctrine_Query::create()
      	->from('User u')
      	->leftJoin('u.Roles r')
      	->orderBy('username ASC')
      	->execute();
      

      Results in following query being generated:

      SELECT `c`.`id` AS `c__id`, `c`.`username` AS `c__username`, `c`.`password` AS `c__password`, `c`.`deleted_at` AS `c__deleted_at`, `c2`.`id` AS `c2__id`, `c2`.`name` AS `c2__name` FROM `core_users` `c` LEFT JOIN `core_users_roles` `c3` ON (`c`.`id` = `c3`.`id_user`) LEFT JOIN `core_roles` `c2` ON `c2`.`id` = `c3`.`id_role` WHERE (`c`.`deleted_at` IS NULL) ORDER BY `c`.`username` ASC, position
      

      As you can see, 'position' column at the end is not prefixed by an alias (should be `c3` here), which may arise problems when there are more than one column of that name used within tables used in the query.

      Same with lazy-loading:

      $query = Doctrine_Query::create()
      	->from('User u')
      	->orderBy('username ASC');
      	
      $result = $query->execute();
      foreach($result as $item)
      {
      	echo count($item->Roles);
      }
      
      SELECT `c`.`id` AS `c__id`, `c`.`name` AS `c__name`, `c2`.`id_user` AS `c2__id_user`, `c2`.`id_role` AS `c2__id_role`, `c2`.`position` AS `c2__position` FROM `core_roles` `c` LEFT JOIN `core_users_roles` `c2` ON `c`.`id` = `c2`.`id_role` WHERE (`c2`.`id_user` IN (?)) ORDER BY position
      

        Activity

        There are no comments yet on this issue.

          People

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

            Dates

            • Created:
              Updated:
              Resolved: