Doctrine 1
  1. Doctrine 1
  2. DC-914

Doctrine_Pager ignores custom COUNT query

    Details

    • Type: Bug Bug
    • Status: Open
    • Priority: Major Major
    • Resolution: Unresolved
    • Affects Version/s: 1.2.3
    • Fix Version/s: None
    • Component/s: Pager
    • Labels:
      None
    • Environment:
      Zend Server CE

      Description

      I found some problem when I tried to define custom query for results counting. Defined custom COUNT query is totally ignored and executed default one. I will give you full description of problem bellow.

      We have following source code:

       
      $q_select = Doctrine_Query::create ()
      ->select ( 'DISTINCT p.product_name AS product_name' )
      ->from ( 'Product p' )
      ->where( 'p.product_name LIKE ?', '%motorola%');
      				
      $q_count = Doctrine_Query::create ()
      ->select ( 'COUNT (DISTINCT p.product_name) num_results' )
      ->from ( 'Product p' )
      ->where( 'p.product_name LIKE ?', '%motorola%');
      												
      $pager = new Doctrine_Pager( $q_select, 1, 25 );										
      $pager->setCountQuery($q_count);
      

      Let's check custom query before calling $pager->execute() method:

       
      echo $pager->getCountQuery(); 
      

      Output:

       
      SELECT COUNT (DISTINCT p.product_name) num_results FROM Product p WHERE p.product_name LIKE ?
      

      Looks like until now is everything is correct. Let's call $pager->execute() method:

       
      $products = $pager->execute(); 
      

      Let's check executed queries using Symfony SQL queries log panel:

      SELECT COUNT(*) AS num_results FROM product p WHERE p.product_name LIKE '%motorola%'
      7.27s, "doctrine" connection
      
      SELECT DISTINCT p.product_name AS p__0 FROM product p WHERE (p.product_name LIKE '%motorola%') LIMIT 25
      3.25s, "doctrine" connection
      

      Executed COUNT query is not same we set using $pager->setCountQuery($q_count). Our defined custom COUNT query is totally ignored and executed default one:

      INSTEAD OF THIS CUSTOM COUNT QUERY:

      SELECT COUNT (DISTINCT p.product_name) num_results FROM Product p WHERE p.product_name LIKE '%motorola%'
      

      EXECUTED DEFAULT COUNT QUERY:

      SELECT COUNT(*) AS num_results FROM product p WHERE p.product_name LIKE '%motorola%'
      

        Activity

        Hide
        Alex Cardoso added a comment -

        I found a possible solution to the problem.

        That occurs not because the Pager countQuery but in a method used inside the Query class.

        When you set the Query or CountQuery for Pager and execute it, it calls a Query method called count(). This method by yourself call another Query class method named Query::getCountSqlQuery().

        This method rather than simply execute the query that you passed earlier, simply create a new query.

        Below is a possible solution to the problem:

        Query.php (Doctrine Stable 1.2.4)

        
        --- Query.php	2011-11-07 20:52:48.000000000 -0200
        +++ Query.php	2011-11-07 20:51:58.000000000 -0200
        @@ -2049,40 +2049,7 @@
                 if (count($this->_queryComponents) == 1 && empty($having)) {
                     $q .= $from . $where . $groupby . $having;
                 } else {
        -
        -            // Subselect fields will contain only the pk of root entity
        -            $ta = $this->_conn->quoteIdentifier($tableAlias);
        -
        -            $map = $this->getRootDeclaration();
        -            $idColumnNames = $map['table']->getIdentifierColumnNames();
        -
        -            $pkFields = $ta . '.' . implode(', ' . $ta . '.', $this->_conn->quoteMultipleIdentifier($idColumnNames));
        -
        -            // We need to do some magic in select fields if the query contain anything in having clause
        -            $selectFields = $pkFields;
        -
        -            if ( ! empty($having)) {
        -                // For each field defined in select clause
        -                foreach ($this->_sqlParts['select'] as $field) {
        -                    // We only include aggregate expressions to count query
        -                    // This is needed because HAVING clause will use field aliases
        -                    if (strpos($field, '(') !== false) {
        -                        $selectFields .= ', ' . $field;
        -                    }
        -                }
        -                // Add having fields that got stripped out of select
        -                preg_match_all('/`[a-z0-9_]+`\.`[a-z0-9_]+`/i', $having, $matches, PREG_PATTERN_ORDER);
        -                if (count($matches[0]) > 0) {
        -                    $selectFields .= ', ' . implode(', ', array_unique($matches[0]));
        -                }
        -            }
        -
        -            // If we do not have a custom group by, apply the default one
        -            if (empty($groupby)) {
        -                $groupby = ' GROUP BY ' . $pkFields;
        -            }
        -
        -            $q .= '(SELECT ' . $selectFields . ' FROM ' . $from . $where . $groupby . $having . ') '
        +            $q .= '( '.$this->getSqlQuery().' ) '
                         . $this->_conn->quoteIdentifier('dctrn_count_query');
                 }
                 return $q;
        
        Show
        Alex Cardoso added a comment - I found a possible solution to the problem. That occurs not because the Pager countQuery but in a method used inside the Query class. When you set the Query or CountQuery for Pager and execute it, it calls a Query method called count(). This method by yourself call another Query class method named Query::getCountSqlQuery(). This method rather than simply execute the query that you passed earlier, simply create a new query. Below is a possible solution to the problem: Query.php (Doctrine Stable 1.2.4) --- Query.php 2011-11-07 20:52:48.000000000 -0200 +++ Query.php 2011-11-07 20:51:58.000000000 -0200 @@ -2049,40 +2049,7 @@ if (count($ this ->_queryComponents) == 1 && empty($having)) { $q .= $from . $where . $groupby . $having; } else { - - // Subselect fields will contain only the pk of root entity - $ta = $ this ->_conn->quoteIdentifier($tableAlias); - - $map = $ this ->getRootDeclaration(); - $idColumnNames = $map['table']->getIdentifierColumnNames(); - - $pkFields = $ta . '.' . implode(', ' . $ta . '.', $ this ->_conn->quoteMultipleIdentifier($idColumnNames)); - - // We need to do some magic in select fields if the query contain anything in having clause - $selectFields = $pkFields; - - if ( ! empty($having)) { - // For each field defined in select clause - foreach ($ this ->_sqlParts['select'] as $field) { - // We only include aggregate expressions to count query - // This is needed because HAVING clause will use field aliases - if (strpos($field, '(') !== false ) { - $selectFields .= ', ' . $field; - } - } - // Add having fields that got stripped out of select - preg_match_all('/`[a-z0-9_]+`\.`[a-z0-9_]+`/i', $having, $matches, PREG_PATTERN_ORDER); - if (count($matches[0]) > 0) { - $selectFields .= ', ' . implode(', ', array_unique($matches[0])); - } - } - - // If we do not have a custom group by, apply the default one - if (empty($groupby)) { - $groupby = ' GROUP BY ' . $pkFields; - } - - $q .= '(SELECT ' . $selectFields . ' FROM ' . $from . $where . $groupby . $having . ') ' + $q .= '( '.$ this ->getSqlQuery().' ) ' . $ this ->_conn->quoteIdentifier('dctrn_count_query'); } return $q;

          People

          • Assignee:
            Guilherme Blanco
            Reporter:
            Arnoldas Lukasevicius
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated: