The default sfDoctrinePager is very very slow when we have large tables. It seems to be something about count totals or so. Here I show a custom Pager I made that works faster and does exactly the same, with a slighty different way to call it.

Here is how you must call it:

$q = Doctrine_Query::create()->select('field1, field2')->from('yourTable');
 
$pager = new customDoctrinePager($q, $page, $rows_per_page);
$pager->init();
 
$results = $pager->getResults();
 

And here you have the class code:

<?php
 
class customDoctrinePager extends sfPager
{
  private $resultSet = null;
 
  public function __construct($query, $page = 1, $maxPerPage = 25)
  {
    $this->setPage($page);
    $this->setMaxPerPage($maxPerPage);
 
    $this->setNbResults($query->count());
 
    $this->setLastPage(ceil($this->getNbResults() / $this->getMaxPerPage()));
 
    $startIndex = (($this->getPage()) - 1) * $maxPerPage;
 
 
    $query->limit($maxPerPage);
    $query->offset($startIndex);
 
    $this->resultSet = $query->execute();
  }
 
  public function init() {}
 
  public function getResults() {
    return $this->resultSet;
  }
 
  protected function retrieveObject($offset) {}
}
 

This is just a version of the nonHydratingPager for Propel, adapted for Doctrine.

<?php
/* Designed to be compatible with sfDoctrinePager only accept raw sql queries instead
 * of a Doctrine_Query object and spit back a resultset which you most likely want to
 * fill an array with instead of the standard array of hydrated objects.
 *
 * This solution is based on Propel's by Noel Tarnoff, Oz Basarir, dev AT (NOSPAM) naturalcapitalDOTorg
 *
 * @author  David Morales, davidmoralesmojica AT (NOSPAM) gmailDOTcom
 *
 * 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 (array of values)
 * 4) sit back and watch the fun
 *
 * ex.
 *  $results = new nonHydratingPager($query_select, $query_count, $page, $max);
 *
 *  foreach( $results as $result )
 *  {
 *    ...
 *  }
 */
 
 
class nonHydratingPager extends sfPager
{
  private $resultSet = null;
 
  public function __construct($query, $query_count, $page = 1, $maxPerPage = 25)
  {
    $this->setPage($page);
    $this->setMaxPerPage($maxPerPage);
 
    $rs = Doctrine_Manager::getInstance()->getCurrentConnection()->fetchAssoc($query_count);
 
    $this->setNbResults($rs[0]['count']);
 
    $this->setLastPage(ceil($this->getNbResults() / $this->getMaxPerPage()));
 
    $startIndex = (($this->getPage()) - 1) * $maxPerPage;
 
 
    $query .= ' LIMIT ' . $maxPerPage . ' OFFSET ' . $startIndex;
 
    $this->resultSet = Doctrine_Manager::getInstance()->getCurrentConnection()->fetchAssoc($query);
  }
 
  public function init() {}
 
  public function getResults() {
    return $this->resultSet;
  }
 
  protected function retrieveObject($offset) {}
}