[DC-914] Doctrine_Pager ignores custom COUNT query Created: 02/Nov/10  Updated: 07/Nov/11

Status: Open
Project: Doctrine 1
Component/s: Pager
Affects Version/s: 1.2.3
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Arnoldas Lukasevicius Assignee: Guilherme Blanco
Resolution: Unresolved Votes: 0
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%'


 Comments   
Comment by Alex Cardoso [ 07/Nov/11 ]

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;
Generated at Sat Nov 29 08:52:14 UTC 2014 using JIRA 6.2.3#6260-sha1:63ef1d6dac3f4f4d7db4c1effd405ba38ccdc558.