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

          People

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

            Dates

            • Created:
              Updated: