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 :

<?php

$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->setStatement($statement);
$pager->setPage($request->getParameter('page', 1));
$pager->init();
$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 :

<?php

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

$pager = new sfPropelPager('Table', 20);
$pager->setCriteria($c);
$pager->setPeerMethod('doSelectStmt');
$pager->setPage($request->getParameter('page', 1));
$pager->init();
$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->setStatement($this->statement);
$this->pager->setPage($request->getParameter(‘page’, 1));
$this->pager->init();

Next Page »



Follow

Get every new post delivered to your Inbox.