[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;
|