Pagination with Zend_Db_Select

Pages, by googleA common (web) interface feature is to divide a long list of items into numbered pages, a technique called pagination. I’ll describe in brief some shortcuts I use with Zend_Db_Select to retrieve the row count and calculate the number of pages for complicated queries.

Pagination of long database result sets is done by retrieving a limited number of rows from a specific offset. For example, if we want each page to display 10 rows, the offset for page 3 would be 21 (page 1 contains rows 1-10, page 2 containts rows 11-20 and so forth. In MySQL, the first row has an offset of 0 (zero), so for the previous example a page 3 in a 10 rows-per-page pagination system would be queried by an offset of 20.

Paginated queries typically look like this:

SELECT * FROM articles LIMIT 20, 10 //Selecting page 3 from table articles (10 rows per page)

In order to generate page navigation, we would need to know the number of pages. We count the total rows that our query generates without the limit clause:

SELECT COUNT(*) FROM articles //Count all rows in table articles

And then we can perform simple arithmetics to calculate the number of pages.

It’s relatively straightforward when dealing with simple queries, however for complicated queries (with multiple JOIN clauses) it can be quite a chore.

Lets switch to the Zend Framework and compose ourselves a somewhat elaborate query using Zend_Db_Select:

$select = new Zend_Db_Select();
$select -> from(array('pil' => 'project_item_list'),array('*'));
	-> join(array('pt' => 'project_tasks'),'',array('task','ptid' => 'id'));
	-> join('projects','',array());
	-> join(array('ptu' => 'projects_to_users'),'',array());
	-> join('users','',array('assignee' => 'name'));
	-> where("pt.project_id='" . $projectId . "'");
	-> where("ptu.user_id='" . $userId . "'");
	-> where("task_id='" . $taskId . "'");
	-> where("pil.assigned_to='" . $assignedTo . "'")
	-> limitPage(3,10);

Plenty of JOIN goodness. You’ll notice the limitPage() call at the end, which is a nice Zend_Db_Select helper to add a limit clause by a page number (in this case it means limit by page 3, 10 rows per page).

So how do we count the number of rows? We’d want to have a COUNT() function returned from the base table (the one that appears at the from clause), however if we mix that with other columns, we’d have to use GROUP BY which would mess up our result-set.

There are two ways to handle this – generating a similar query sans the columns and the limit clauses, or using the SQL_CALC_FOUND_ROWS() function. The first approach results in two seperate queries while the second is just one query, however it is not that obvious which one is better for performance. For our case we’ll use the first method, duplicating the original query while stripping out whats needed.

We could recompose the entire query using copy-paste methods, removing the columns and the limit clauses, but we would like a more general solution. I extend Zend_Db_Select and add a simple utility method:

class Techfounder_Db_Select extends Zend_Db_Select {
	public function prepareForCount($countColName = 'count') {
		$this -> reset(Zend_Db_Select::COLUMNS);
		$this -> reset(Zend_Db_Select::LIMIT_COUNT);
		$this -> reset(Zend_Db_Select::LIMIT_OFFSET);
		$table = key($this -> _parts['from']);
		$this -> _parts['columns'] = array(
			0 => array(
				0 => $table,
				1 => new Zend_Db_Expr('COUNT(*)'),
				2 => $countColName
		return $this;

The prepareForCount() method modifies a select object to strip all previously set columns and LIMIT clauses, and to set a COUNT() function on the base table in the query (the row count is returned as a key named ‘count’). We can now use this Select class to do the following:

$select = new Techfounder_Db_Select();
... //Multiple JOIN query, as composed previously

$countSelect = clone $select;
$countSelect -> prepareForCount();
$result = $db -> fetchRow($countSelect); //$db is an instance of Zend_Db_Adapter
$pageCount = ((int)(($result['count'] - 1) / $rowCount)) + 1; //Using the number of rows to calculate the number of pages.
//$rowCount is the number of rows per page

I go even a step further, and encapsulate this short process in a countPages() method in a custom wrapper for Zend_Db_Select (called Zend_Db_Gateway, for which I submitted a proposal and would talk about in my conclusion to my Models in the Zend Framework series).

To know when the next article is published, please subscribe to new articles using your Email below or follow me on Twitter.

Subscribe to Blog via Email

Enter your email address to receive notification about new posts.