Smart paging for an array of elements using sfPropelCustomJoinHelper

Say, you want to create a paging from a query. For some reasons, you do not want to select all the fields. There are several solutions that can be used, namely : Pager for array of objects and Pager for an array of elements v.2.
Unfortunately, both of methods mentioned above is still not optimal solutions. As donharold said,

Your way of doing it requires to load all the data from your datasource first. This will multiply the load for your database connection (or other datasource) and the memory usage for the server compared to paged loading of data. For low traffic sites with small data tables this might not be an issue, but it will significantly degrade performance for most sites, and in extreme cases (large tables and several concurrent users) can even make your server run out of memory.

So, how to paging the data without load all the data from datasource first?
sfPropelCustomJoinHelper can help us solve the problem. If you never use this class before, please read this post Applying custom joins using a helper class first.

Say, an ‘article’ table has the following fields: id, title, content, created_at, updated_at, and we just want to select id, title and content fields.

In your actions..

$c = new Criteria();
$c->addSelectColumn(ArticlePeer::ID);
$c->addSelectColumn(ArticlePeer::TITLE);
$c->addSelectColumn(ArticlePeer::CONTENT);
$joinHelper = new mySfPropelCustomJoinHelper('Article');
$pager = new myPropelPager('Article', 10);
$pager->setCriteria($c);
$pager->setClassPeer($joinHelper);
$pager->setPeerMethod('doSelectSomeFields');
$pager->setPage($this->getRequestParameter('page',1));
$pager->init();
$this->pager = $pager;

Where myPropelPager is the class mentioned in the linked post.

<?php
class mySfPropelCustomJoinHelper extends sfPropelCustomJoinHelper
{
   public function doSelectSomeFields($criteria, $con=null)
   {
     $c = clone $criteria;
     $rs = call_user_func_array(array(
     $this->mainClassName.'Peer', 'doSelectRS'),  array($c, $con));
     return $rs;
   }
}

If you are using propel 1.3, change ‘doSelectRS‘ with ‘doSelectStmt‘. In order to make sfPropelCustomJoinHelper class compatible with propel 1.3, you can refer to my previous article

and in your template…

for Propel 1.2

<?php $rs = $pager->getResults(); ?>
<?php while ($rs->next()): ?>
  <tr>
    <td><?php echo $rs->getInt(1) ?></td>
    <td><?php echo $rs->getString(2) ?></td>
    <td><?php echo $rs->getString(3) ?></td>
  </tr>
<?php endwhile; ?>

for Propel 1.3

<?php $rs = $pager->getResults(); ?>
<?php while ($row = $rs->fetch(PDO::FETCH_NUM)): ?>
  <tr>
    <td><?php echo $row[0] ?></td>
    <td><?php echo $row[1] ?></td>
    <td><?php echo $row[2] ?></td>
  </tr>
<?php endwhile; ?>
<?php $rs->closeCursor() ?>
Advertisements

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: