Doctrine 1
  1. Doctrine 1
  2. DC-28

Duplicate join condition when using nestedSet

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 1.1.4, 1.2.0-ALPHA1
    • Fix Version/s: 1.1.4, 1.2.0-ALPHA2
    • Component/s: Nested Set, Query
    • Labels:
      None

      Description

      When trying to fetch a nested set tree, where a base query uses join, an exception is thrown "'SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens'", which is caused by incorrect generated SQL query.

      How to reproduce:

      $query = Doctrine_Query::create()
                  ->select('s.name')
                  ->from('Test s')
                  ->leftJoin('s.Test2 st WITH st.title = ?', array(1));
      
      $tree_table = Doctrine::getTable('Test');
      $tree = $tree_table->getTree();
      

      viewing SQL at this point, is:

      SELECT t.id AS t__id, t.name AS t__name 
      FROM test t 
      LEFT JOIN test_2 t2 
          ON t.id = t2.test_id AND (t2.title = ?)
      

      set the query as a base for nestedSet tree:

      $tree->setBaseQuery($query);
      

      viewing SQL at this point, is:

      SELECT t.id AS t__id, t.name AS t__name, t.lft AS t__lft, t.rgt AS t__rgt, t.level AS t__level 
      FROM test t 
      LEFT JOIN test_2 t2 
          ON t.id = t2.test_id AND (t2.title = ?) AND (t2.title = ?)
      

      As you can see lft/rgt/level fields were added, which is ok, but also additional LEFT JOIN condition was duplicated - (t2.title = ?) .

      These kind of queries are working in 1.0* branch and also seemed to work in 1.1* branch until 5680 revision, which broke it. Although Changeset 5700 seems to must have fixed it, but the SQL is still generated doubled in 1.1* and 1.2* branches.

        Activity

        Hide
        Jacek Jędrzejewski added a comment -

        TestCase from ticket 2105 (which was fixed in rev. 5700) works ok. (BTW. It was my ticket )

        Create a failing testcase for that, it will help devs.

        Show
        Jacek Jędrzejewski added a comment - TestCase from ticket 2105 (which was fixed in rev. 5700) works ok. (BTW. It was my ticket ) Create a failing testcase for that, it will help devs.
        Hide
        Viktoras added a comment -
        
        class Doctrine_Ticket_Dc28_TestCase extends Doctrine_UnitTestCase 
        {
            public function prepareTables()
            {
                $this->tables[] = 'Ticket_Dc28_Tree';
                parent::prepareTables();
            }
        
            public function testQuery()
            {
                try {
                    $q = Doctrine_Query::create()
                        ->select('a.id, t.lang')
                        ->from('Ticket_Dc28_Tree a')
                        ->innerJoin('a.Translation t WITH t.name != ?', 'test')
                        ;
                    $q->execute();
                    //echo $q->getSql().PHP_EOL;
                    
                    $this->assertEqual(
                        $q->getSql(), 
                        'SELECT t.id AS t__id, t2.id AS t2__id, t2.lang AS t2__lang '.
                        'FROM ticket__dc28__tree t '.
                        'INNER JOIN ticket__dc28__tree_translation t2 '.
                        'ON t.id = t2.id AND (t2.name != ?)'
                    );
                    
                    //echo $q->getSql().PHP_EOL;
                    $tree_table = Doctrine::getTable('Ticket_Dc28_Tree');
                    $tree = $tree_table->getTree();
                    $tree->setBaseQuery($q);
                    //echo $q->getSql().PHP_EOL;
                    
                    $this->assertEqual(
                        $q->getSql(), 
                        'SELECT t.id AS t__id, t.lft AS t__lft, t.rgt AS t__rgt, t.level AS t__level, t2.id AS t2__id, t2.lang AS t2__lang '.
                        'FROM ticket__dc28__tree t '.
                        'INNER JOIN ticket__dc28__tree_translation t2 '.
                        'ON t.id = t2.id AND (t2.name != ?)'
                    );
                    
                    //$this->pass();
                } catch (Exception $e) {
                    $this->fail($e->getMessage());
                }
            }
        }
        
        class Ticket_Dc28_Tree extends Doctrine_Record
        {
            public function setTableDefinition()
            {
                $this->hasColumn('name', 'string', 255);
            }
        
            public function setUp()
            {
                $i18n = new Doctrine_Template_I18n(array('fields' => array(0 => 'name')));
                $this->actAs($i18n);
                $this->actAs('NestedSet');
            }
        }
        
        Show
        Viktoras added a comment - class Doctrine_Ticket_Dc28_TestCase extends Doctrine_UnitTestCase { public function prepareTables() { $ this ->tables[] = 'Ticket_Dc28_Tree'; parent::prepareTables(); } public function testQuery() { try { $q = Doctrine_Query::create() ->select('a.id, t.lang') ->from('Ticket_Dc28_Tree a') ->innerJoin('a.Translation t WITH t.name != ?', 'test') ; $q->execute(); //echo $q->getSql().PHP_EOL; $ this ->assertEqual( $q->getSql(), 'SELECT t.id AS t__id, t2.id AS t2__id, t2.lang AS t2__lang '. 'FROM ticket__dc28__tree t '. 'INNER JOIN ticket__dc28__tree_translation t2 '. 'ON t.id = t2.id AND (t2.name != ?)' ); //echo $q->getSql().PHP_EOL; $tree_table = Doctrine::getTable('Ticket_Dc28_Tree'); $tree = $tree_table->getTree(); $tree->setBaseQuery($q); //echo $q->getSql().PHP_EOL; $ this ->assertEqual( $q->getSql(), 'SELECT t.id AS t__id, t.lft AS t__lft, t.rgt AS t__rgt, t.level AS t__level, t2.id AS t2__id, t2.lang AS t2__lang '. 'FROM ticket__dc28__tree t '. 'INNER JOIN ticket__dc28__tree_translation t2 '. 'ON t.id = t2.id AND (t2.name != ?)' ); //$ this ->pass(); } catch (Exception $e) { $ this ->fail($e->getMessage()); } } } class Ticket_Dc28_Tree extends Doctrine_Record { public function setTableDefinition() { $ this ->hasColumn('name', 'string', 255); } public function setUp() { $i18n = new Doctrine_Template_I18n(array('fields' => array(0 => 'name'))); $ this ->actAs($i18n); $ this ->actAs('NestedSet'); } }
        Hide
        Jonathan H. Wage added a comment -

        This is now fixed in Doctrine 1.1 and 1.1.

        1.0 is not affected.

        Show
        Jonathan H. Wage added a comment - This is now fixed in Doctrine 1.1 and 1.1. 1.0 is not affected.

          People

          • Assignee:
            Jonathan H. Wage
            Reporter:
            Viktoras
          • Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: