Doctrine 1
  1. Doctrine 1
  2. DC-523

Aggregating values in select(), always joins a record, even if there is no relation

    Details

    • Type: Bug Bug
    • Status: Resolved
    • Priority: Critical Critical
    • Resolution: Can't Fix
    • Affects Version/s: 1.2.1
    • Fix Version/s: None
    • Component/s: None
    • Labels:
      None
    • Environment:
      mysql5, unix

      Description

      Example:

      I want to aggregate a expression, so i need to select everything:

      ->select('a., d., CONCAT_WS(" ", d.initials, d.surname) as formalName))
      ->from('Account a')
      ->leftJoin('a.Details d')

      Result will be an empty record of Details if there is no relation, removing the aggregated value will fix this and not join a record.

        Activity

        Hide
        Jonathan H. Wage added a comment -

        Sorry, I don't quite understand with such minimal information. Can you provide a failing test case?

        Show
        Jonathan H. Wage added a comment - Sorry, I don't quite understand with such minimal information. Can you provide a failing test case?
        Hide
        pbijl added a comment - - edited
         
        
        // logical example
        // result: working as expected
        $q = Doctrine_Query::create()
        ->from('Accounts a')
        ->leftJoin('a.Data d')
        ->execute()
        ;
        
        // this time the same query but with a ambigious select clause
        // result: working as expected, 
        $q = Doctrine_Query::create()
        ->select('a.*, d.*')
        ->from('Accounts a')
        ->leftJoin('a.Data d')
        ->execute()
        ;
        
        // same query, but there should not be a Data row because of the bogus WITH condition
        // result: working as expected
        $q = Doctrine_Query::create()
        ->select('a.*, d.*')
        ->from('Accounts a')
        ->leftJoin('a.Data d with d.type = "blabla"')
        ->execute()
        ;
        
        // same query, but with an aggregated column
        // result: working as expected, again, no Data row
        $q = Doctrine_Query::create()
        ->select('a.*, d.*, d.surname as test')
        ->from('Accounts a')
        ->leftJoin('a.Data d with d.type = "blabla"')
        ->execute()
        ;
        
        // same query, but with an aggregated functional expression
        // result: FAILS. `test` is joined as an empty column in the Accounts record, AND the Data record, which results in an empty Data record 
        // you can imagine saving the Accounts object to save an empty Data record in return
        $q = Doctrine_Query::create()
        ->select('a.*, d.*, concat_ws(" ", d.firstname, d.surname) as test')
        ->from('Accounts a')
        ->leftJoin('a.Data d with d.type = "blabla"')
        ->execute()
        ;
        
        // a expression that doesnt concatenates doesnt join an empty Data record, but does aggregate a empty `test` column on Accounts
        $q = Doctrine_Query::create()
        ->select('a.*, d.*, trim(d.firstname) as test')
        ->from('Accounts a')
        ->leftJoin('a.Data d with d.type = "blabla"')
        ->execute()
        ;
        
        
        Show
        pbijl added a comment - - edited // logical example // result: working as expected $q = Doctrine_Query::create() ->from('Accounts a') ->leftJoin('a.Data d') ->execute() ; // this time the same query but with a ambigious select clause // result: working as expected, $q = Doctrine_Query::create() ->select('a.*, d.*') ->from('Accounts a') ->leftJoin('a.Data d') ->execute() ; // same query, but there should not be a Data row because of the bogus WITH condition // result: working as expected $q = Doctrine_Query::create() ->select('a.*, d.*') ->from('Accounts a') ->leftJoin('a.Data d with d.type = "blabla"') ->execute() ; // same query, but with an aggregated column // result: working as expected, again, no Data row $q = Doctrine_Query::create() ->select('a.*, d.*, d.surname as test') ->from('Accounts a') ->leftJoin('a.Data d with d.type = "blabla"') ->execute() ; // same query, but with an aggregated functional expression // result: FAILS. `test` is joined as an empty column in the Accounts record, AND the Data record, which results in an empty Data record // you can imagine saving the Accounts object to save an empty Data record in return $q = Doctrine_Query::create() ->select('a.*, d.*, concat_ws(" ", d.firstname, d.surname) as test') ->from('Accounts a') ->leftJoin('a.Data d with d.type = "blabla"') ->execute() ; // a expression that doesnt concatenates doesnt join an empty Data record, but does aggregate a empty `test` column on Accounts $q = Doctrine_Query::create() ->select('a.*, d.*, trim(d.firstname) as test') ->from('Accounts a') ->leftJoin('a.Data d with d.type = "blabla"') ->execute() ;
        Hide
        Jonathan H. Wage added a comment -

        Hi, you can find information about how to create a valid Doctrine unit test case here: http://www.doctrine-project.org/documentation/manual/1_2/en/unit-testing

        Show
        Jonathan H. Wage added a comment - Hi, you can find information about how to create a valid Doctrine unit test case here: http://www.doctrine-project.org/documentation/manual/1_2/en/unit-testing
        Hide
        pbijl added a comment - - edited
        <?php
        
        class Doctrine_AggregateFunctionalExpressios_TestCase extends Doctrine_UnitTestCase 
        {
        	private function baseQueryForAllMethods() {
        		return Doctrine_Query::create()
        		->from('User u')
        		->where('u.id = ?', 4)
        		;
        	}
            public function testAggregateColumnInParentModel()
            {
        		$res = $this->baseQueryForAllMethods()
        		->select('u.*, concat(u.name, u.loginname) as aggregatedColumn, p.*')
        		->leftJoin('u.Phonenumber p')		
        		->fetchOne()
        		;
        		$this->assertTrue(isset($res->aggregatedColumn));
        		$this->assertFalse(isset($res->Phonenumber->aggregatedColumn));
        	}	
            public function testAggregateColumnInJoinedModel()
            {
        		$res = $this->baseQueryForAllMethods()
        		->select('u.*, p.*, concat(p.phonenumber, p.entity_id) as aggregatedColumn')
        		->leftJoin('u.Phonenumber p')		
        		->fetchOne()
        		;
        		$this->assertTrue(isset($res->Phonenumber->aggregatedColumn));
        		$this->assertFalse(isset($res->aggregatedColumn));
        	}
            public function testAggregateColumnInJoinedModelWithFailingWhereClause()
            {
        		$res = $this->baseQueryForAllMethods()
        		->select('u.*, p.*, concat(p.phonenumber, p.entity_id) as aggregatedColumn')
        		->leftJoin('u.Phonenumber p WITH p.id = ?', 100)		
        		->fetchOne()
        		;
        		$this->assertFalse(isset($res->Phonenumber->aggregatedColumn));		
        		$this->assertFalse(isset($res->aggregatedColumn));				
        	}
        }
        
        Show
        pbijl added a comment - - edited <?php class Doctrine_AggregateFunctionalExpressios_TestCase extends Doctrine_UnitTestCase { private function baseQueryForAllMethods() { return Doctrine_Query::create() ->from('User u') ->where('u.id = ?', 4) ; } public function testAggregateColumnInParentModel() { $res = $this->baseQueryForAllMethods() ->select('u.*, concat(u.name, u.loginname) as aggregatedColumn, p.*') ->leftJoin('u.Phonenumber p') ->fetchOne() ; $this->assertTrue(isset($res->aggregatedColumn)); $this->assertFalse(isset($res->Phonenumber->aggregatedColumn)); } public function testAggregateColumnInJoinedModel() { $res = $this->baseQueryForAllMethods() ->select('u.*, p.*, concat(p.phonenumber, p.entity_id) as aggregatedColumn') ->leftJoin('u.Phonenumber p') ->fetchOne() ; $this->assertTrue(isset($res->Phonenumber->aggregatedColumn)); $this->assertFalse(isset($res->aggregatedColumn)); } public function testAggregateColumnInJoinedModelWithFailingWhereClause() { $res = $this->baseQueryForAllMethods() ->select('u.*, p.*, concat(p.phonenumber, p.entity_id) as aggregatedColumn') ->leftJoin('u.Phonenumber p WITH p.id = ?', 100) ->fetchOne() ; $this->assertFalse(isset($res->Phonenumber->aggregatedColumn)); $this->assertFalse(isset($res->aggregatedColumn)); } }
        Hide
        pbijl added a comment -

        testcase attached~

        Show
        pbijl added a comment - testcase attached~
        Hide
        Jonathan H. Wage added a comment -

        Your test case has some errors in it. The ->Phonenumber relationship is a many so it is a Doctrine_Collection. Also, this is all expected behavior for aggregates to be located in the root. They only exist in the relationship as well for BC reasons. The whole functionality is flawed a bit and can't be patched without breaking backwards compatibility. All these issues have been thought through and addressed in Doctrine 2

        Show
        Jonathan H. Wage added a comment - Your test case has some errors in it. The ->Phonenumber relationship is a many so it is a Doctrine_Collection. Also, this is all expected behavior for aggregates to be located in the root. They only exist in the relationship as well for BC reasons. The whole functionality is flawed a bit and can't be patched without breaking backwards compatibility. All these issues have been thought through and addressed in Doctrine 2

          People

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

            Dates

            • Created:
              Updated:
              Resolved: