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.

19 Comments

  1. Hey there,

    I’ve just received a link to this post in the mail and I created an issue in the ZF bug tracker to look into the problems you’re describing here. (see http://framework.zend.com/issues/browse/ZF-4052)

    In the mean while you could try updating to the latest Paginator version from SVN. There have been quite a few bug fixes since RC2. In case the bug is still present in the latest version from SVN, you can also supply a custom count query through the setRowCount() method on the DbSelect adapter until the issue has been addressed.

    Hopefully this will work out for you! 🙂

  2. I runned into this problem not so long ago while implementing my own pagination for ZEND. And i found what i think is actually an even easier solution that doesn’t seem to have advert effects at least for my work load. Im using Mysql as my DB but this should be valid sql

    $select = $DBTABLE->select()->setIntegrityCheck( false );
    $select->join …
    $select->group ….

    $countsql = “SELECT count(*) FROM (“.$select->__toString().”)”
    $count = $db->fetchOne( $countsql );

  3. I’ve been playing with your proposed fix just now. Unfortunately it makes the unit tests throw some exceptions, so I’m not going to include it as-is.

    Could you come up with a solution that still allows the unit tests to pass? It would be greatly appreciated.

  4. How will used jquery effects on zend framework
    any reference site it have
    or give the best turorial for this

  5. Great blog here! Also your web site loads up very fast! What
    web host are you using? Can I get your affiliate link to your host?

    I wish my website loaded up as fast as yours lol

Leave a Reply to J. Stutterheim Cancel reply

Your email address will not be published. Required fields are marked *