Details
-
Type:
Bug
-
Status:
Open
-
Priority:
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
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-914, expand=changesets[-21:-1].revisions[0:29],reviews}, methodType=GET}] : Received status code 503 (Service Temporarily Unavailable)
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;