Join Problems with Zend_Paginator and Zend_Db_Select objects

Zend_Paginator is one of the newest members of Zend framework family. It’s surely a rich addition with Zend’s power. Moreover, It’s handling the main data of pagination, not only pagination links. Which I didn’t found in any other pagination library.

But, when I (and many other developers) start to working with this library, found many problems/bugs. The matter is, they are being solved very fast and hope to get a bullet-proof pagination library soon. For example, here is tow important fixings about Zend_Paginator and Zend_Db_Table_Select.

I had to face a problem with Zend_Paginator when using Join in Zend_Db_Select object. I am explaining here how the problem arises and how to solve it.

The Problem

One of the 4 Adapters for Zend_Paginator is DbSelect which uses a Zend_Db_Select instance. Now, the problem occurred if I need to have some calculative data from other tables and use join with the Zend_Db_Select object for them. For example, I am retrieving data from `deals` table and collecting number of comments from `comments` table and total votes from `votes` table. So, with other conditions, I had to add this lines with select object:

   1: $select->joinLeft(array('c' => 'comments'), 'deals.id = c.deal_id', array('total_comments'=> 'count(c.id)') );

   2: $select->joinLeft(array('v' => 'votes'),    'delas.id = v.deal_id', array('total_votes'   => 'count(v.id)') );

   3: $select->group($this->_name . '.id');

Now, the Zend_Paginator will always calculate the TotalRows as 1. That means, $this->totalItemCount; will always print 1 in “view partial“.

Why it happens?

Let’s take a look to the count function of DbSelect Adaptor of Zend_Paginator:

   1: public function count()

   2: {

   3:     if ($this->_rowCount === null) {

   4:         $expression = new Zend_Db_Expr('COUNT(*) AS ' . self::ROW_COUNT_COLUMN);

   5:         

   6:         $rowCount   = clone $this->_select;

   7:         $rowCount->reset(Zend_Db_Select::COLUMNS)

   8:                  ->reset(Zend_Db_Select::ORDER)

   9:                  ->reset(Zend_Db_Select::LIMIT_OFFSET)

  10:                  ->columns($expression);

  11:                  

  12:         $this->setRowCount($rowCount);

  13:     }

  14:  

  15:     return $this->_rowCount;

  16: }

Here, at line 7, the column list is being removed. But we have a `Group By` clause in our select object which is not cleared by this function and the result is always 1. At this point, if we add “->reset(Zend_Db_Select::GROUP)” after line 9, the calculation will get rid from always being 1. But there will arise another problem. That is, joined tables are still remaining in `From` clause. You can see them by adding “die(print_r($rowCount->getPart(Zend_Db_Select::FROM)));” after line 10. This join tables will make the calculation inconsistence because there is no expression columns now to summarize their result.

The Solution

Just alter this function as following to prevent this problem:

   1: public function count()

   2: {

   3:     if ($this->_rowCount === null) {

   4:         $expression = new Zend_Db_Expr('COUNT(*) AS ' . self::ROW_COUNT_COLUMN);

   5:        

   6:         $rowCount   = clone $this->_select;

   7:         $rowCount->reset(Zend_Db_Select::FROM);

   8:         

   9:         foreach($this->_select->getPart(Zend_Db_Select::FROM) as $name => $table)

  10:         {

  11:             if(empty($table['joinCondition']))        

  12:             {

  13:                 $rowCount->from(array($name => $table['tableName']));

  14:             }

  15:             elseif($table['joinType'] == 'inner join')        

  16:
       {

  17:                 $rowCount->join(array($name => $table['tableName']), $table['joinCondition']);

  18:             }

  19:         }

  20:         

  21:         $rowCount->reset(Zend_Db_Select::COLUMNS)

  22:                  ->reset(Zend_Db_Select::ORDER)

  23:                  ->reset(Zend_Db_Select::GROUP)

  24:                  ->reset(Zend_Db_Select::LIMIT_OFFSET)

  25:                  ->columns($expression);

  26:  

  27:         $this->setRowCount($rowCount);

  28:     }

  29:  

  30:     return $this->_rowCount;

  31: }

At line 9, I am running a foreach which will remove the `from` clause and again assign only the main table and inner join tables. And, at line 23, just removing the `Group By` clause from query. Now, this function will provide right calculation even when there is some joins like this. But, if you use inner join in your Zend_Db_Select object (which is very rare for pagination), this function may not serve properly.

If there is any mistake in this alternation or any other way can make it better, please let me know. And… let’s meet power with simplicity.

UPDATE [26/08/08] : The function is updated and hopefully it will work fine for inner joins too.

UPDATE [24/09/08] : This issue is solved in ZF 1.6. Jurrien Stutterheim has reported about this post in Zend Framework Issue tracker and worked on it. After the release of 1.6, he requested me to check if this issue still exist. I’ve checked and found it solved. Thanks to Jurriën Stutterheim.