Issues

ZF-6330: Zend_Paginator_Adapter_DbSelect make incorrect SQL query when use join keyword

Issue Type: Bug Created: 2009-04-16T17:55:17.000+0000 Last Updated: 2009-06-18T20:13:43.000+0000 Status: Resolved Fix version(s): - 1.8.4 (23/Jun/09)

Reporter: Nikolay Goldschmidt (ccppprogrammer) Assignee: Jurrien Stutterheim (norm2782) Tags: - Zend_Paginator

Related issues: Attachments:

Description

Hello,

OS: centos x64 PHP: PHP 5.1.6 (cli) (built: Apr 7 2009 08:00:18) x64 DB: psql (PostgreSQL) 8.3.7 x64

Zend_Paginator_Adapter_DbSelect make incorrect SQL query when use join keyword. It make few columns as argument that passed to DISTINCT function than DISTINCT function make multi column result that COUNT function didn't understand.

The error is: {quote} "Fatal error: Uncaught exception 'Zend_Db_Statement_Exception' with message 'SQLSTATE[42883]: Undefined function: 7 ERROR: function count(bigint, character varying, character varying) does not exist at character 8 HINT: No function matches the given name and argument types. You might need to add explicit type casts." when use Zend_Paginator with joinLeft functions. {quote} Postgresql log: ERROR: function count(bigint, character varying, character varying) does not exist at character 8 HINT: No function matches the given name and argument types. You might need to add explicit type casts. STATEMENT:

<pre class="highlight">
SELECT COUNT(DISTINCT "a"."id","a"."name","a"."description") AS "zend_paginator_row_count" FROM "a"
         LEFT JOIN "c" ON c.a_id = a.id
         LEFT JOIN "b" ON c.b_id = b.id

My model for table "a" is:

<pre class="highlight">
class A extends Zend_Db_Table_Abstract
{
    protected $_name = 'a';
    protected $_primary = 'id';
    public function fetchAllPaginator($pageNumber = 1, $itemCountPerPage = 20)
    {
        $select = $this->select()
        ->from("$this->_schema.$this->_name", array('id' => 'a.id', 'name' => 'a.name', 'description' => 'a.description'))
        ->joinLeft('c', "c.a_id = $this->_schema.$this->_name.id", array())
        ->joinLeft('b', "c.b_id = b.id", array('b_count' => 'COUNT(b.id)'))
        ->group(array('id' => 'a.id', 'name' => 'a.name', 'description' => 'a.description'))
        ->order("$order")
        ->setIntegrityCheck(false);

        $paginator = Zend_Paginator::factory($select);
        Zend_View_Helper_PaginationControl::setDefaultViewPartial('index/paginator.phtml');
        Zend_Paginator::setDefaultScrollingStyle('Sliding');
        $paginator->setItemCountPerPage($itemCountPerPage);
        $paginator->setPageRange(Zend_Registry::get('configuration')->pagerange);
        $paginator->setCurrentPageNumber($pageNumber);

        return $paginator;
    }
}

Comments

Posted by Jurrien Stutterheim (norm2782) on 2009-04-16T18:32:25.000+0000

Could you try this with the ZF 1.8.0 alpha release and see if this issue still exists?

Posted by Nikolay Goldschmidt (ccppprogrammer) on 2009-04-17T05:35:33.000+0000

The problem exists in ZendFramework-1.8.0a1 too.

Posted by Nikolay Goldschmidt (ccppprogrammer) on 2009-04-17T06:33:31.000+0000

I write patch that solve my problem:

<pre class="highlight"> 
--- Zend/Paginator/Adapter/DbSelect.php-       2009-02-22 02:25:39.000000000 +0300
+++ Zend/Paginator/Adapter/DbSelect.php        2009-04-17 17:28:19.000000000 +0400
@@ -191,6 +191,7 @@
                 $groupPart = implode(',', $groupParts);
             }

+            if (count($groupParts) > 0) $groupPart = $groupParts[0];
             $countPart  = empty($groupPart) ? 'COUNT(*)' : 'COUNT(DISTINCT ' . $groupPart . ')';
             $expression = new Zend_Db_Expr($countPart . ' AS ' . $db->quoteIdentifier(self::ROW_COUNT_COLUMN));

Posted by Matthew Ratzloff (mratzloff) on 2009-06-08T14:20:12.000+0000

I don't think randomly removing group clauses is the desired functionality. ;-)

Posted by Matthew Ratzloff (mratzloff) on 2009-06-08T14:22:09.000+0000

This isn't a blocking issue, so I'm lowering priority to critical. An alternative exists: passing a query to setRowCount().

Posted by Jurrien Stutterheim (norm2782) on 2009-06-18T20:13:42.000+0000

Resolved in r16151, merged to release-1.8 in r16152

Have you found an issue?

See the Overview section for more details.

Copyright

© 2006-2016 by Zend, a Rogue Wave Company. Made with by awesome contributors.

This website is built using zend-expressive and it runs on PHP 7.

Contacts