<?php
/* Designed to be compatible with sfPropelPager only accept raw sql queries instead
 * of a criteria object and spit back a resultset which you most likely want to
 * fill an array with instead of the standard array of hydrated objects.
 *
 * @author  Noel Tarnoff, Oz Basarir dev AT (NOSPAM) naturalcapitalDOTorg
 *
 * Typical usage scenario:
 * 1) build 2 query strings with common WHERE clause, one for count one for selecting the rows
 * 2) pass the queries in with page and maxPerPage into the constructor ( no need to ->init() )
 * 3) iterate through your result set and build your array of values
 * 4) sit back and watch the fun
 *
 * ex.
 *  $objPager = new nonHydratingPager($query_select, $query_count, $page, $max);
 *
 *  $rs = $objPager->getResultSet();
 *
 *  $arrEntities = array();
 *  while ( $rs->next() ) {
 *     $arrEntities[$] = array('field1'=>$rs->getString(1),
 *                             'field2'=>$rs->getString(2),
 *                             'field3'=>$rs->getString(3));
 *  }
 */
 
 
class nonHydratingPager extends sfPager
{
private
  $resultSet = null;
 
  public function __construct($query, $query_count, $page = 1, $maxPerPage = 25)
  {
    $this->setPage($page);
    $this->setMaxPerPage($maxPerPage);
 
    $con = Propel::getConnection();
 
    $stmt = $con->prepareStatement($query_count);
    $rs = $stmt->executeQuery(ResultSet::FETCHMODE_NUM);
    $rs->next();
 
    $this->setNbResults($rs->get(1));
 
    $this->setLastPage(ceil($this->getNbResults() / $this->getMaxPerPage()));
 
    $startIndex = (($this->getPage()) - 1) * $maxPerPage;
 
 
    $query .= ' LIMIT ' . $maxPerPage . ' OFFSET ' . $startIndex;
    $stmt = $con->prepareStatement($query);
 
    $this->resultSet = $stmt->executeQuery(ResultSet::FETCHMODE_NUM);
  }
 
  public function init() {}
 
  public function getResults() {
    return $this->resultSet;
  }
 
  protected function retrieveObject($offset) {}
 
}

I'm sure you've been there; you want to use Symfony's native sfPropelPager addon but you have to seed it with a complicated query that can't be reproduced via Criteria. In order to tackle this issue, I created a subclass of sfPropelPager called sfAdvancedPropelPager (it could probably benefit from a better name).

PLEASE NOTE: You will have to change all private methods and properties in sfPropelPager from 'private' to 'protected'. This won't break paging elsewhere in your projects, I promise you! What it will do is allow this subclass to gain access to it's parent's data and functionality. It should be noted that this addon was developed with the current stable version (0.6.3) of Symfony in mind.

I've cited the "search engine" example within the Askeet project as a suitable candidate for demonstration, albeit with some tweaks. See http://www.symfony-project.com/askeet/21 for details!!

<?php
...
public static function search($phrase, $exact = false, $offset = 0, $max = 10)
{
  $words    = array_values(myTools::stemPhrase($phrase));
  $nb_words = count($words);
 
  if (!$words)
  {
    return array();
  }
 
  $con = sfContext::getInstance()->getDatabaseConnection('propel');
 
  // define the base query
  $query = '
      SELECT DISTINCT '.SearchIndexPeer::QUESTION_ID.', COUNT(*) AS nb, SUM('.SearchIndexPeer::WEIGHT.') AS total_weight
      FROM '.SearchIndexPeer::TABLE_NAME;
 
  if (sfConfig::get('app_permanent_tag'))
  {
    $query .= '
      WHERE ';
  }
  else
  {
    $query .= '
      LEFT JOIN '.QuestionTagPeer::TABLE_NAME.' ON '.QuestionTagPeer::QUESTION_ID.' = '.SearchIndexPeer::QUESTION_ID.'
      WHERE '.QuestionTagPeer::NORMALIZED_TAG.' = ? AND ';
  }
 
  $query .= '
      ('.implode(' OR ', array_fill(0, $nb_words, SearchIndexPeer::WORD.' = ?')).')
      GROUP BY '.SearchIndexPeer::QUESTION_ID;
 
  // AND query?
  if ($exact)
  {
    $query .= '
      HAVING nb = '.$nb_words;
  }
 
  $query .= '
      ORDER BY nb DESC, total_weight DESC';
 
  // prepare the statement
  $stmt = $con->prepareStatement($query);
 
  $placeholder_offset = 1;
  if (sfConfig::get('app_permanent_tag'))
  {
    $stmt->setString(1, sfConfig::get('app_permanent_tag'));
    $placeholder_offset = 2;
  }
  for ($i = 0; $i < $nb_words; $i++)
  {
    $stmt->setString($i + $placeholder_offset, $words[$i]);
  }
 
  // time to call our shiny new pager!
  $pager = new sfAdvancedPropelPager('Question', $max);
  $pager->setStatement($stmt);
  $pager->setPage(ceil($offset / $max));
  $pager->init();
  return $pager;
}
...
?>

You would then assign the $pager to a suitable property in your action, and then handle your pagination in your template in the usual fashion as illustrated on <http://www.symfony-project.com/book/trunk/pager> - simple as that!

<?php
 
class sfAdvancedPropelPager extends sfPropelPager
{
  protected
    $statement = null;
 
  public function __construct($class, $defaultMaxPerPage = 10)
  {
    $this->setClass($class);
    $this->setMaxPerPage($defaultMaxPerPage);
    $this->setPage(1);
    $this->parameter_holder = new sfParameterHolder();
    $this->setPeerMethod('retrieveByPk');
  }  
 
  public function init()
  {
    require_once(sfConfig::get('sf_model_lib_dir').'/'.$this->getClassPeer().'.php');
 
    $rs = clone $this->getStatement()->executeQuery(ResultSet::FETCHMODE_NUM);
 
    $this->setNbResults($rs->getRecordCount());
 
    if (($this->getPage() == 0 || $this->getMaxPerPage() == 0))
    {
      $this->setLastPage(0);
    }
    else
    {
      $this->setLastPage(ceil($this->getNbResults() / $this->getMaxPerPage()));
      $this->statement->setOffset(($this->getPage() - 1) * $this->getMaxPerPage());
      $this->statement->setLimit($this->getMaxPerPage());
    }
  }
 
  public function getResults()
  {
    $rs = $this->getStatement()->executeQuery(ResultSet::FETCHMODE_NUM);
 
    $objects = array();
 
    while ($rs->next())
    {
      $objects[] = call_user_func(array($this->getClassPeer(), $this->getPeerMethod()), $rs->getInt(1));
    }
 
    return $objects;
  }
 
  protected function retrieveObject($offset)
  {
    $statement = clone $this->getStatement();
    $statement->setOffset($offset - 1);
    $statement->setLimit(1);
 
    $rs = $statement->executeQuery(ResultSet::FETCHMODE_NUM);
 
    $object = null;
    while ($rs->next())
    {
      $object = call_user_func(array($this->getClassPeer(), $this->getPeerMethod()), $rs->getInt(1));
    }
 
    return $object;
  }
 
  public function getStatement()
  {
    return $this->statement;
  }
 
  public function setStatement($stmt)
  {
    $this->statement = $stmt;
  }
}
 
?>

This is helper is mostly inspired by Pagination navigation helper, but there are some differences:

• only list (ul/li)
• emphasize the actual page we are browsing
• stylish is done with pure css
• all parameters of the original request are kept in uri when paginating (article/list/category_id/5/page/3 instead of article/list/page/3)
• we only need 1 parameter (current uri is guessed)

PaginationHelper.php

function pagination($pager)
{
    $uri = sfRouting :: getInstance()->getCurrentInternalUri();
    $html = '';
 
    if ($pager->haveToPaginate())
    {
        $uri .= strstr($uri, '?') ? '&page=' : '?page=';
 
        if ($pager->getPage() != 1)
        {
            $html .= '<li>' . link_to('first', $uri . '1') . '</li>';
            $html .= '<li>' . link_to('previous', $uri . $pager->getPreviousPage()) . '</li>';
        }
 
        foreach ($pager->getLinks() as $page)
        {
            if ($page == $pager->getPage())
                $html .= '<li><strong>' . link_to($page, $uri . $page) . '</strong></li>';
            else
                $html .= '<li>' . link_to($page, $uri . $page) . '</li>';
        }
 
        if ($pager->getPage() != $pager->getLastPage())
        {
            $html .= '<li>' . link_to('next', $uri . $pager->getNextPage()) . '</li>';
            $html .= '<li>' . link_to('last', $uri . $pager->getLastPage()) . '</li>';
        }
 
        $html = '<ul class="pagination">' . $html . '</ul>';
    }
 
    return $html;
}

Minimal CSS

ul.pagination li {
    display: inline;
    list-style-type: none;
    padding-right: 1em;
}

In your template

<?php echo use_helper('Pagination') ?>
<?php echo pagination($pager) ?>

The problem

Let us take the example of snipeet! :-) The function that selects snippets by tags is a custom SQL query. When filtering by tags there is no pager anymore.

Why is that? The problem is that sfPropelPaginate only works with a Criteria object, not with a raw query. As a result you cannot have pagination with raw SQL query which is a rather severe limitation.

The solution

The solution is to first encode the parameter of the query in the criteria (that's the tricky bit) and then to set up a custom peer method.

As i said earlier we will take a simplified version of snipeet as an example. We assume that we have a function that creates a sql statement from a list of tags.

Here we go about setting the dummy criteria that encodes an array of tags:

getCriteriaFromTags($tags)
{
  $c = new Criteria();
  for ($i = 1; $i <= count($tags); ++$i)
  {
    $prefix = 't'.$i;
    // note that that SnippetTagPeer::TABLE_NAME  SnippetTagPeer::NAME are in fact arbitrary
    // any other table and table.column combination would work as well
    $c->addAlias($prefix.SnippetTagPeer::TABLE_NAME, SnippetTagPeer::TABLE_NAME);
    $c->add($prefix.SnippetTagPeer::NAME, $tags[$i-1]);
  }
  return $c;
}

Now you can initialise a sfPropelPaginate object as usual with that Criteria object. When that is done you tell it to use your custom peer method:

$pager->setPeerMethod('doSelectByTags');

Next you code the doSelectByTags method that performs the actual selection from a criteria object:

function doSelectByTags($c)
{
  // first you fetch the tags from the query
  $tags = array();
  foreach($c->keys() as $key)
    $tags[] = $c->get($key);
 
  // here comes your custom sql query 
  // it basically creates a $statement variable from the $tags array
  // .....
 
  // now you have to add the limit and offset:
 $statement->setLimit($c->getLimit());
 $statement->setOffset($c->getOffset());
 // and the rest of the code is as usual
}

In a template displaying a paginated list, you need to show the pager navigation. Create a PaginationHelper.php in lib/helper:

<?php
 
function pager_navigation($pager, $uri)
{
  $navigation = '';
 
  if ($pager->haveToPaginate())
  {  
    $uri .= (preg_match('/\?/', $uri) ? '&' : '?').'page=';
 
    // First and previous page
    if ($pager->getPage() != 1)
    {
      $navigation .= link_to(image_tag('/sf/images/sf_admin/first.png', 'align=absmiddle'), $uri.'1');
      $navigation .= link_to(image_tag('/sf/images/sf_admin/previous.png', 'align=absmiddle'), $uri.$pager->getPreviousPage()).' ';
    }
 
    // Pages one by one
    $links = array();
    foreach ($pager->getLinks() as $page)
    {
      $links[] = link_to_unless($page == $pager->getPage(), $page, $uri.$page);
    }
    $navigation .= join('  ', $links);
 
    // Next and last page
    if ($pager->getPage() != $pager->getLastPage())
    {
      $navigation .= ' '.link_to(image_tag('/sf/images/sf_admin/next.png', 'align=absmiddle'), $uri.$pager->getNextPage());
      $navigation .= link_to(image_tag('/sf/images/sf_admin/last.png', 'align=absmiddle'), $uri.$pager->getLastPage());
    }
 
  }
 
  return $navigation;
}

In your templates, display the pagination links like that:

<?php echo use_helper('Pagination') ?>
<?php echo pager_navigation($mypager, '@myrule') ?>