Doctrine 1
  1. Doctrine 1
  2. DC-313

Ordering m2m relationship with column from related table (with orderBy option)

    Details

    • Type: Bug Bug
    • Status: Open
    • Priority: Blocker Blocker
    • Resolution: Unresolved
    • Affects Version/s: 1.2.0
    • Fix Version/s: None
    • 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

      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.

        Activity

        Maciej Hołyszko created issue -
        Hide
        Maciej Hołyszko added a comment -

        Attached test case.

        Show
        Maciej Hołyszko added a comment - Attached test case.
        Maciej Hołyszko made changes -
        Field Original Value New Value
        Attachment DC313TestCase.php [ 10202 ]
        Hide
        Maciej Hołyszko added a comment -

        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.

        Show
        Maciej Hołyszko added a comment - 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.
        Maciej Hołyszko made changes -
        Priority Major [ 3 ] Blocker [ 1 ]
        Hide
        suhock added a comment - - edited

        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.

        Show
        suhock added a comment - - edited 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.
        Hide
        Bart W added a comment -

        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.

        Show
        Bart W added a comment - 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.
        Hide
        suhock added a comment -

        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.

        Show
        suhock added a comment - 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.

        This list may be incomplete, as errors occurred whilst retrieving source from linked applications:

        • Request to http://www.doctrine-project.org/fisheye/ failed: Error in remote call to 'FishEye 0 (http://www.doctrine-project.org/fisheye/)' (http://www.doctrine-project.org/fisheye) [AbstractRestCommand{path='/rest-service-fe/search-v1/crossRepositoryQuery', params={query=DC-313, expand=changesets[0:20].revisions[0:29],reviews}, methodType=GET}] : Received status code 503 (Service Temporarily Unavailable)

          People

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

            Dates

            • Created:
              Updated: