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%'