[DC-313] Ordering m2m relationship with column from related table (with orderBy option) Created: 02/Dec/09  Updated: 18/Feb/11

Status: Open
Project: Doctrine 1
Component/s: Record, Relations
Affects Version/s: 1.2.0
Fix Version/s: None

Type: Bug Priority: Blocker
Reporter: Maciej Hołyszko Assignee: Jonathan H. Wage
Resolution: Unresolved Votes: 3
Labels: None
Environment:

php 5.3/win, doctrine 1.2 svn, ATTR_QUOTE_IDENTIFIER = true, ATTR_USE_DQL_CALLBACKS = true


Attachments: File DC313TestCase.php    

 Description   

I find no way to define automatic orderBy in m2m relations with column not from reference table, but actual related table.

E.g. BlogPost <= m2m through BlogPostCategory => BlogCategory
I need BlogPost->Categories ordered by BlogCategory.name

class BlogPost extends Doctrine_Record
{
    public function setTableDefinition()
    {
        $this->hasColumn('title', 'string', 128);
        $this->hasColumn('content', 'string');
    }
    
    public function setUp()
    {
        $this->hasMany('BlogCategory as BlogCategories', array('local' => 'id_blog_post', 'foreign' => 'id_blog_category', 'refClass' => 'BlogPostCategory', 'orderBy' => 'name'));        
    }
}

class BlogCategory extends Doctrine_Record
{
    public function setTableDefinition()
    {
        $this->hasColumn('name', 'string', 128);
    }
    
    public function setUp()
    {
        $this->hasMany('BlogPost as BlogPosts', array('local' => 'id_blog_category', 'foreign' => 'id_blog_post', 'refClass' => 'BlogPostCategory'));
    }
}

class BlogPostCategory extends Doctrine_Record
{
    public function setTableDefinition()
    {
        $this->hasColumn('id_blog_post', 'integer', null, array('primary' => true));
        $this->hasColumn('id_blog_category', 'integer', null, array('primary' => true));
    }
    
    public function setUp()
    {
        $this->hasOne('BlogPost', array('local' => 'id_blog_post', 'foreign' => 'id', 'onDelete' => 'CASCADE'));
        $this->hasOne('BlogCategory', array('local' => 'id_blog_category', 'foreign' => 'id', 'onDelete' => 'CASCADE'));
    }
}

The resulting query contains doubled 'name' column in ORDER BY clause, both from reference table and related table, e.g. ORDER BY t2.name, t3.name

I tried putting the following code in BlogCategory::setTableDefinition() instead of attribute in relation definition in BlogPost record:

$this->option('orderBy', 'name');

but the result was the same.

Maybe I'm doing something wrong? Is there a possibility to define an alias, where to get column name from - in orderBy attribute?

Thanks in advance.



 Comments   
Comment by Maciej Hołyszko [ 02/Dec/09 ]

Attached test case.

Comment by Maciej Hołyszko [ 08/Dec/09 ]

I find this issue as critical one now, because when I use e.g.

$this->option('orderBy', 'name');

in a model's definition (not ref class), then when other model is related m2m with it, a query loading both of them with relations will fail because of name column duplicated in ref table.

Comment by suhock [ 23/Apr/10 ]

I am having the same issue with an equivalent test case. For some reason, the 'orderBy' option on the target of the join (set by calling the option() function inside the setUp() method of the model class, not the ref class) is being applied to the relation table. After digging through the 1.2.2 tag a bit, I found altering line 1319 of Query.php as follows seems to fix the problem (at least against my test cases):

if (isset($map['relation'])) {
if (isset($map['relation']) && $map['relation']->getTable() === $map['table']) {

I'll do some more thorough testing and submit a patch if I find time.

Comment by Bart W [ 17/Feb/11 ]

I had this issue as well. suhock's solution fixed it for me. It would be nice if this was merged in to a bug fix release of Doctrine 1.x.

Comment by suhock [ 18/Feb/11 ]

I ended up creating a new ticket, DC-651, which addresses a more general problem with the orderBy feature. You should use the attached Ticket_DC651.patch instead, as I found the solution I provided here is not completely correct and does not pass all test cases.

Generated at Thu Oct 23 19:29:27 UTC 2014 using JIRA 6.2.3#6260-sha1:63ef1d6dac3f4f4d7db4c1effd405ba38ccdc558.