[REFACTORED] Enabling pagination with raw SQL queries

In my previous post, I explained how to use sfPager to paginate through raw SQL queries, introducing the statementPager class.

Even if this class helped me to solve problems, I’ve never been so proud of it since this class does not behave like a real pager. In fact, I should have named it statementChunker.

In order to paginate through resultsets, the class have to  fetch all resultsets until we reach the desired page, only returning an array of  corresponding resultsets. So this class doesn’t really ‘paginate’, resulting a memory outage when working with large resulsets.

Here is a typical complex query I want to paginate, done the statementPager way :


$connection = Propel::getConnection();
$query = 'SELECT SUBSTRING_INDEX(field, "/", 1) AS field 
FROM table 
WHERE other_field LIKE '%pattern%' 
GROUP BY field 
ORDER BY sub';
$statement = $connection->prepare($query);
$pager = new statementPager(null, 10);
$pager->setPage($request->getParameter('page', 1));
$results = $pager->getResults();

I recently discovered that Propel allows you build custom criterias. I’ve got everything out-of-the-box to run the same query the Propel way, with native SQL pagination :


$c = new Criteria;
$c->add(TablePeer::FIELD, '%pattern%', Criteria::LIKE);
$c->addAsColumn('field', 'SUBSTRING_INDEX('.TablePeer::FIELD.', "/", 1) AS field');

$pager = new sfPropelPager('Table', 20);
$pager->setPage($request->getParameter('page', 1));
$results = $pager->getResults()->fetchAll();

That’s a pretty valuable refactoring session : one class less to maintain and a performance boost as an outcome ! (and also a good lesson in humility)

$this->pager = new statementPager(null, 20);
$this->pager->setPage($request->getParameter(‘page’, 1));

  1. Nice!
    Could you post the doctrine version?


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: