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'),'pt.id=pil.task_id',array('task','ptid' => 'id'));
	-> join('projects','projects.id=pt.project_id',array());
	-> join(array('ptu' => 'projects_to_users'),'ptu.project_id=projects.id',array());
	-> join('users','users.id=pil.assigned_to',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.

  • s

    Your blog seems to contain information about developing with FOSS (PHP and ZF).
    … And I guess you are Israeli.

    I wonder why your blog isn’t part of Planet FOSS-IL: http://planet.linux.org.il/ .

  • s

    Your blog seems to contain information about developing with FOSS (PHP and ZF).
    … And I guess you are Israeli.

    I wonder why your blog isn’t part of Planet FOSS-IL: http://planet.linux.org.il/ .

  • Eran Galperin

    I have to admit I never heard of it before…
    My main development platform is Windows, so I’m not sure if planet-linux is the best place to publish my articles. Also, from looking at the feeds there they all seem linux centered which is not really related to the themes I write about (apart from being open-source). What do you think?

  • Eran Galperin

    I have to admit I never heard of it before…
    My main development platform is Windows, so I’m not sure if planet-linux is the best place to publish my articles. Also, from looking at the feeds there they all seem linux centered which is not really related to the themes I write about (apart from being open-source). What do you think?

  • s

    Hi again,

    It’s called planet FOSS-IL (Free Open Source Software), not planet Linux-IL.
    The domain name is like that because that’s what was available for the community use.

    IMO your blog belongs there because it talks very often about using PHP and other FOSS web technologies. A lot of people’s blogs in the FOSS-Planet talk about Python and Ruby and even Free Pascal. Not many talk about PHP and that’s a shame.

    You should probably contact Lior Kaplan (google for his email. I don’t have it here with me) and ask him about it. I think he’s the one in charge of it.

  • s

    Hi again,

    It’s called planet FOSS-IL (Free Open Source Software), not planet Linux-IL.
    The domain name is like that because that’s what was available for the community use.

    IMO your blog belongs there because it talks very often about using PHP and other FOSS web technologies. A lot of people’s blogs in the FOSS-Planet talk about Python and Ruby and even Free Pascal. Not many talk about PHP and that’s a shame.

    You should probably contact Lior Kaplan (google for his email. I don’t have it here with me) and ask him about it. I think he’s the one in charge of it.

  • Daniel Kladnik

    There is a faster way to count rows.

    First, select your data with
    select SQL_CALC_FOUND_ROWS id, title, whatever ……. limit number, offset

    Then use “select FOUND_ROWS()” query to retrieve the total number of rows in the first query. Limit at the end does not affect the total rows number of rows in the second query.

  • Daniel Kladnik

    There is a faster way to count rows.

    First, select your data with
    select SQL_CALC_FOUND_ROWS id, title, whatever ……. limit number, offset

    Then use “select FOUND_ROWS()” query to retrieve the total number of rows in the first query. Limit at the end does not affect the total rows number of rows in the second query.

  • http://www.techfounder.net Eran Galperin

    If you’d notice, in my article I linked to discussion about which method is better for performance on the MySQL Performance Blog. It is not necessarily faster to use SQL_CALC_FOUND_ROWS.

  • http://www.techfounder.net Eran Galperin

    If you’d notice, in my article I linked to discussion about which method is better for performance on the MySQL Performance Blog. It is not necessarily faster to use SQL_CALC_FOUND_ROWS.

  • Daniel Kladnik

    I haven’t noticed, i apologize. I just scanned the article, I’m actually here because of your zend model articles… waiting for the third one :)

  • Daniel Kladnik

    I haven’t noticed, i apologize. I just scanned the article, I’m actually here because of your zend model articles… waiting for the third one :)

  • http://www.techfounder.net Eran Galperin

    Hopefully soon ;)

  • http://www.techfounder.net Eran Galperin

    Hopefully soon ;)