Zend Framework

• Type: Bug
• Status: Resolved
• Priority: Critical
• Resolution: Fixed
• Affects Version/s: 1.7.8
• Fix Version/s: 1.8.4
• Component/s: Zend_Paginator
• Labels:

  None

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:

"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.

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

• Assignee:

  Jurrien Stutterheim

  Reporter:

  Nikolay Goldschmidt

• Created:

  16/Apr/09 5:55 PM

  Updated:

  18/Jun/09 8:13 PM

  Resolved:

  18/Jun/09 8:13 PM