ActiveRecord and Zend_Paginator_Adapter_Interface

by Clay vanSchalkwijk on October 29, 2009

Zend has a lot of tools to help speed up the application development process.  One such tool I found useful, was Paginator.   I am using php-activerecord in my project using Zend_Framework as the backend, to tie the two together is very simple.  Paginator requires two methods, it needs to be able to pull a count to get the total and it also needs to be able to pull in a subset of the data.  Take a look at the following example:

 
<?php
class My_Paginator implements Zend_Paginator_Adapter_Interface {
	public function __construct($table,$conditions = array())
	{
		if(!is_array($conditions))
 
			$conditions = array( $conditions );
 
		$this->conditions = $conditions;
 
		$this->table	  = new $table;
 
	}
 
	public function getItems($offset, $itemCountPerPage)
	{
			return $this->table->find('all', array('limit' => $itemCountPerPage, 'offset' => $offset, 'conditions' => $this->conditions));
	}
 
	public function count()
	{
		return $this->table->count(array('conditions' => $this->conditions));
	}
}
?>
 

The two methods the Zend_Paginator_Adapter_Interface expects are count() and getItems(). The above example is a little "raw", it should serve to guide you in what to do when extending the Paginator with it's own adapter regardless of what your database layer is. In the case of $conditions, these are the parameters you are passing to SQL:

 
$paginator = new Zend_Paginator(new My_Paginator('User',' active = "Y" '));
 

We want to access the User model, and only want to pull out users who are active. Easy enough, you can certainly put in more complicated SQL here, but for a general use purpose it solves 99% of what I want with ActiveRecord and paging, pass in a model to the adapter, and some basic conditions for listing.

 
    public function pageUserAction()
    {
 		$paginator = new Zend_Paginator(new My_Paginator('User',' active = "Y" '));
		$paginator->setCurrentPageNumber($this->_getParam('page', 1));
		$paginator->setItemCountPerPage('25');
		$this->view->paginator = $paginator;
    }
 

This is a basic usage of Zend_Paginator, you are passing in the current page you are on, and the results per page and pushing it out to the view, and on the view side:

 
<div id="userlist">
<?php if (count($this->paginator)){
	foreach ($this->paginator as $user){
		echo $user->username."<br>";
	}
}
?>
</div>
 
<?= $this->paginationControl($this->paginator, 'Elastic', '/common/paginator.phtml'); ?>
 

If anyone has any problems or questions getting the two to work together let me know in the comments and I will do my best to answer your questions.

4 comments

This approach might not work for you without the ability to count independent of your query.

getItems() should only return a subset since it is the slice that is displayed, therefore you can’t mix count() which should represent the total number of records matching your query with the subset, it will always be wrong since the affected rows will always be your subset (the number or results per page($itemCountPerPage).

Unless you can create an independent counting function that can return the total number of rows you might be out of luck. I’m not sure how big your data set is, but querying everything just to get a count might not be feasible.

by Clay vanSchalkwijk on November 20, 2009 at 2:31 pm. Reply #

my getitems is returning only a subset and my count represents total number of rows that match the query, i.e., ” count 789 rows — now displaying 50 – 70″
The most efficient way is to issue these 2 queries in succession:

// get a subset:
SELECT SQL_CALC_FOUND_ROWS *
FROM table
WHERE somecondtion
LIMIT 49, 20

// count of all records that would match query
SELECT FOUND_ROWS()

by Andrew on November 21, 2009 at 7:33 pm. #

You could work around it and just pass in the page and count per page into the construct and query for it up-front.

public function __construct(page,itemperpage,conditions){
// get a subset:
SELECT SQL_CALC_FOUND_ROWS *
FROM table
WHERE somecondtion
LIMIT offset, itemperpage

// count of all records that would match query
SELECT FOUND_ROWS()

this->result = result;
this->count = count;
}

public function getItems()
{
return $this->result;
}

public function count()
{
return $this->count;
}

Not the most elegant solution, but it would work.

by Clay vanSchalkwijk on November 21, 2009 at 7:58 pm. #

>>The two methods the Zend_Paginator_Adapter_Interface expects are count() and getItems()

It appears that count() is called first, which is a problem for me because I set the count value in getItems() , so when count() is called, there’s no count yet.
I’m trying to adapt this for use outside the Zend Framework.

[code]

public function getItems($offset, $itemCountPerPage)
{
/* modify sql; stick the SQL_CALC_FOUND_ROWS in there: */
$sql_modified = substr_replace($this->sql, ' SQL_CALC_FOUND_ROWS ', 6, 0) . " LIMIT $offset, $itemCountPerPage";
$stmt = $this->pdo->prepare($sql_modified);
$stmt->execute($params);

/* set count property: */
$this->count = $this->pdo->query('SELECT FOUND_ROWS()')->fetchColumn(0);

/* return data: */
return $stmt->fetchAll(PDO::FETCH_OBJ);
}

public function count()
{
return $this->count;
}

[/code]

by andrew warner on November 19, 2009 at 7:43 pm. Reply #

Leave your comment

Required.

Required. Not published.

If you have one.