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




[DC-897] Pager ignores default model hasMany ORDER BY statements, caused by getLimitSubquery ignoring same Created: 22/Oct/10  Updated: 10/Nov/10

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

Type: Bug Priority: Major
Reporter: Andrew Eross Assignee: Guilherme Blanco
Resolution: Unresolved Votes: 0
Labels: None

Attachments: Text File Doctrine_Query.php.ORDERBY.patch    

 Description   

Our model configuration includes several hasMany statements, for example:

$this->hasMany('Subcategory as Subcategories', array(
'refClass' => 'SubcategoryTone',
'local' => 'tone_id',
'foreign' => 'subcategory_id',
'cascade' => array('delete'),
'orderBy' => 'order_id',
));

We noticed that the ORDER BY directive worked just fine with a normal query, but the order by was being ignored when we fed it into the Pager.

For example:
$aa = $t->execute(array(), Doctrine_Core::HYDRATE_ARRAY);
var_dump($aa[4]);

$pager = new Doctrine_Pager($t, $currentPage, $resultsPerPage);
$bb = $pager->execute(array(), Doctrine_Core::HYDRATE_ARRAY);

var_dump($bb[4]);

These two var_dumps would give different results because the ORDER BY is ignored by the limit subquery in the pager.



 Comments   
Comment by Andrew Eross [ 22/Oct/10 ]

I've also found a fix for the issue (thanks to George over here for finding the location of the problem) ... we found that simply moving the ORDER BY generation code inside of buildSqlQuery() to be ABOVE the if block containing getLimitSubquery() resolves the issue.

We're not super familiar with the Doctrine code-base, so everything looks to work fine after moving the code block, and it fixes the issue, but would love to hear if this is a real fix.

diff from 1.2.3 via our SVN:

Index: Query.php
===================================================================
— Query.php (revision 1120)
+++ Query.php (working copy)
@@ -1256,7 +1256,46 @@
$this->_sqlParts['where'][] = '(' . $string . ')';
}
}
+
+ // Fix the orderbys so we only have one orderby per value
+ foreach ($this->_sqlParts['orderby'] as $k => $orderBy) {
+ $e = explode(', ', $orderBy);
+ unset($this->_sqlParts['orderby'][$k]);
+ foreach ($e as $v)

{ + $this->_sqlParts['orderby'][] = $v; + }

+ }

+ // Add the default orderBy statements defined in the relationships and table classes
+ // Only do this for SELECT queries
+ if ($this->_type === self::SELECT) {
+ foreach ($this->_queryComponents as $alias => $map) {
+ $sqlAlias = $this->getSqlTableAlias($alias);
+ if (isset($map['relation'])) {
+ $orderBy = $map['relation']->getOrderByStatement($sqlAlias, true);
+ if ($orderBy == $map['relation']['orderBy']) {
+ if (isset($map['ref']))

{ + $orderBy = $map['relation']['refTable']->processOrderBy($sqlAlias, $map['relation']['orderBy'], true); + }

else

{ + $orderBy = null; + }

+ }
+ } else

{ + $orderBy = $map['table']->getOrderByStatement($sqlAlias, true); + }

+
+ if ($orderBy) {
+ $e = explode(',', $orderBy);
+ $e = array_map('trim', $e);
+ foreach ($e as $v) {
+ if ( ! in_array($v, $this->_sqlParts['orderby']))

{ + $this->_sqlParts['orderby'][] = $v; + }

+ }
+ }
+ }
+ }
+
$modifyLimit = true;
$limitSubquerySql = '';

@@ -1307,47 +1346,8 @@

$q .= ' WHERE ' . $limitSubquerySql . $where;
// . (($limitSubquerySql == '' && count($this->_sqlParts['where']) == 1) ? substr($where, 1, -1) : $where);

  • }
    + }
  • // Fix the orderbys so we only have one orderby per value
  • foreach ($this->_sqlParts['orderby'] as $k => $orderBy) {
  • $e = explode(', ', $orderBy);
  • unset($this->_sqlParts['orderby'][$k]);
  • foreach ($e as $v) { - $this->_sqlParts['orderby'][] = $v; - }
  • }
    -
  • // Add the default orderBy statements defined in the relationships and table classes
  • // Only do this for SELECT queries
  • if ($this->_type === self::SELECT) {
  • foreach ($this->_queryComponents as $alias => $map) {
  • $sqlAlias = $this->getSqlTableAlias($alias);
  • if (isset($map['relation'])) {
  • $orderBy = $map['relation']->getOrderByStatement($sqlAlias, true);
  • if ($orderBy == $map['relation']['orderBy']) {
  • if (isset($map['ref'])) { - $orderBy = $map['relation']['refTable']->processOrderBy($sqlAlias, $map['relation']['orderBy'], true); - }

    else

    { - $orderBy = null; - }
  • }
  • } else { - $orderBy = $map['table']->getOrderByStatement($sqlAlias, true); - }

    -

  • if ($orderBy) {
  • $e = explode(',', $orderBy);
  • $e = array_map('trim', $e);
  • foreach ($e as $v) {
  • if ( ! in_array($v, $this->_sqlParts['orderby'])) { - $this->_sqlParts['orderby'][] = $v; - }
  • }
  • }
  • }
  • }
    -
    $q .= ( ! empty($this->_sqlParts['groupby'])) ? ' GROUP BY ' . implode(', ', $this->_sqlParts['groupby']) : '';
    $q .= ( ! empty($this->_sqlParts['having'])) ? ' HAVING ' . implode(' AND ', $this->_sqlParts['having']): '';
    $q .= ( ! empty($this->_sqlParts['orderby'])) ? ' ORDER BY ' . implode(', ', $this->_sqlParts['orderby']) : '';
    @@ -1396,7 +1396,7 @@
    $subquery = 'SELECT DISTINCT ';
    }
    $subquery .= $this->_conn->quoteIdentifier($primaryKey);
    -
    +
    // pgsql & oracle need the order by fields to be preserved in select clause
    if ($driverName == 'pgsql' || $driverName == 'oracle' || $driverName == 'oci' || $driverName == 'mssql' || $driverName == 'odbc') {
    foreach ($this->_sqlParts['orderby'] as $part)
    Unknown macro: {@@ -1420,7 +1420,7 @@ // don't add primarykey column (its already in the select clause) if ($part !== $primaryKey) { $subquery .= ', ' . $partOriginal; - }+ }


    }
    }
    }

Property changes on: Query.php
___________________________________________________________________
Deleted: svn:keywords

  • Id Revision
    Deleted: svn:eol-style
  • LF
Comment by Andrew Eross [ 22/Oct/10 ]

Diff file

Comment by Andrew Eross [ 10/Nov/10 ]

patch -p0 ./libs/doctrine/Doctrine/Query.php ./Doctrine_Query.php.ORDERBY.patch





[DC-739] Pager returns incorrect page number when constructed on query with HAVING Created: 16/Jun/10  Updated: 16/Jun/10

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

Type: Bug Priority: Major
Reporter: Paweł Barański Assignee: Guilherme Blanco
Resolution: Unresolved Votes: 0
Labels: None
Environment:

Ubuntu 9.10,
Apache/MySQL,
Framework: Symfony 1.4



 Description   

//method returns those categories that have any products, it returns 3 records
public function getCategoriesWithProductsQuery()

{ $query = $this->createQuery('z')->select( 'z.*, COUNT(p.id) prod_number' ) ->leftJoin( 'z.AdvertiseProducts p' )->groupBy( 'z.id' )->having('prod_number > 0'); return $query; }

//but when i do count() on created pager it returns 8 records (which is equal to categories in my database):

$doctrinePager = $this->getServiceContainer()
->setParameter('doctrine_pager.model', 'ZwCategory')
->getService('doctrine_pager')
>setMaxPerPage($this>maxPerPage)
->setQuery($query)
>setPage(null === $page ? $this>page : $page)
->init();
var_dump(count($doctrinePager));

//pager works, but it "thinks" that there are more pages than in reality. with maxPerPage = 2, it thinks that there are 4 pages, but looking at query result it //should be only 2 pages.






[DC-544] Doctrine_Pager bug with Oracle. getNumResults doesn't return the correct number of records Created: 04/Mar/10  Updated: 18/Mar/10

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

Type: Bug Priority: Major
Reporter: Bertrand Zuchuat Assignee: Guilherme Blanco
Resolution: Unresolved Votes: 0
Labels: None

Attachments: File DC544TestCase.php    

 Description   

The function getNumResults() doesn't work correctly.

This is my model class

class ReviewListTable extends Doctrine_Table
{
 public function getAvailable()
 {
   return $this->createQuery()
   ->select('DISTINCT(to_char(file_date, \'YYYY-MM-DD\')) fdate')
   ->orderBy('fdate DESC');
 }
}

If i execute this fonction, the number of record is 31. If i use the Pager, the result is 5744 records

Controler:

$this->pager = new Doctrine_Pager(
                           Doctrine_Core::getTable('ReviewList')->getAvailable(),
                           $request->getParameter('page', 1),
                           20);

View:

$rs = $this->pager->execute();
echo $pager->getNumResults();


 Comments   
Comment by Jonathan H. Wage [ 15/Mar/10 ]

Hmm. I don't quite understand. Can you make a test case?





[DC-312] Doctrine_Pager query "influences" Doctrine::getTable() query Created: 02/Dec/09  Updated: 02/Dec/09

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

Type: Bug Priority: Major
Reporter: Jan Matousek Assignee: Guilherme Blanco
Resolution: Unresolved Votes: 0
Labels: None
Environment:

Debian testing squeeze 32 bit.



 Description   

When I use the same procedure without Doctrine_Pager everything works fine, with Doctrine_Pager the behavior of later queries is strange. Example:
----------------WORKING:
1. I fetch list
Doctrine_Query::create()>select('z.,s.,o.*,u.jmeno as Vložil,k.nazev_cs as Kategorie')>from('ShopZbozi z,z.Obrazek o,z.Soubor s,z.Uzivatel u,z.ShopKategorie k')>orderby('z.nazev_cs ASC')>fetchArray();

2. I fetch a row to edit independently on the list above
$object = Doctrine::getTable('ShopZbozi')->find(123);

3. edit properties of the object...

4. $object->replace();
the object is fetched with no related objects like [Uzivatel] or [Soubor], so when I change for example $object->iduzivatel (related Uzivatel object id) and save it is OK

----------------BROKEN:
1. I fetch list in Pager
new Doctrine_Pager(Doctrine_Query::create()>select('z.,s.,o.*,u.jmeno as Vložil,k.nazev_cs as Kategorie')>from('ShopZbozi z,z.Obrazek o,z.Soubor s,z.Uzivatel u,z.ShopKategorie k')>orderby('z.nazev_cs ASC'),$this>page,$this->perpage);

2. I fetch a row to edit independently on the list above (in contrast to the first working example this $object is fetched with EMPTY RELATION OBJECTS, which causes inserting empty new rows when calling ->replace())
$object = Doctrine::getTable('ShopZbozi')->find(123);

3. edit properties of the object...

4. $object->replace();
-> the $object has all the related object fetched as it was defined in the list in Pager - like it was used as a template for getTable and when ->replace() - new empty related objects are inserted in DB.

I've been using Doctrine for I guess 2 years now and I believe this bug/strange behavior has always been there.
Maybe I'm wrong and it is not a bug, but the behavior is strange and it took me really long to find where the problem is and I still don't know how to use pager without influencing the later queries.
Thanx John Mathew






[DC-188] Pager breaks when HAVING clause references existing table column Created: 04/Nov/09  Updated: 20/Jul/10

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

Type: Bug Priority: Major
Reporter: Benedict Bacayon Assignee: Guilherme Blanco
Resolution: Unresolved Votes: 0
Labels: None
Environment:

Local: Windows/Cygwin/XAMPP
Stage & Prod: CentOS
Apache / MySQL
Framework: Zend



 Description   

Pager breaks when HAVING clause references any existing table column.

Query used:

$q = Doctrine_Query::create()
->select("u.id, u.first_name, u.last_name, u.is_active")
->addSelect('COUNT(u.id) as rolecount')
->from('User u')
->leftJoin('u.Roles r')
->groupBy('u.id')
->having("rolecount > 2 and u.is_active = 1");

Execution of query by itself works fine (eg via execute(), fetch...)

When putting this query in a Doctrine_Pager, error outputs:

$pager = new Doctrine_Pager(
$q,
$currentPage,
$resultsPerPage
);

$pager->execute();

Error Received:
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'u.is_active' in 'having clause'



 Comments   
Comment by ryan [ 20/Jul/10 ]

a quick fix for this is to enclose the field in parentheses when selecting it, then use an alias to reference it inside the having clause. eg

$q = Doctrine_Query::create()
->select("u.id, u.first_name, u.last_name, (u.is_active) u_is_active")
->addSelect('COUNT(u.id) as rolecount')
->from('User u')
->leftJoin('u.Roles r')
->groupBy('u.id')
->having("rolecount > 2 and u_is_active = 1");





Generated at Mon Oct 20 22:49:46 UTC 2014 using JIRA 6.2.3#6260-sha1:63ef1d6dac3f4f4d7db4c1effd405ba38ccdc558.