Issues

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

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:


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:


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

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

The problem exists in ZendFramework-1.8.0a1 too.

I write patch that solve my problem:

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

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

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

Resolved in r16151, merged to release-1.8 in r16152