I needed a one pager for custom SQL and i read the sfPropelPager and i change like this.

Howto use: write two SQL in Peer class like this.

   public function getReportsWithPager($params)
   {
 
         $WHERE = "FROM ".self::TABLE_NAME." WHERE ".self::SOMEWHEREFIELD." = '%s'";
 
          $SQL ="SELECT SUM(".self::FIELDNAME."), ".self::WHOIS." %s";    
          $cSQL = "SELECT COUNT(id) %s"  
          $WHERE = sprintf($WHERE, $params['where']);
          $SQL = sprintf($SQL, $WHERE);
          $cSQL = sprintf($cSQL, $WHERE);
 
         $pager = new myCustomPager($cSQL, $SQL, $params['maxpage']);
         $pager->setPage($params['page']);
 
         $pager->init();
         return $pager;          
   }

And call in your action:

       $this->pager = SomePeer::getReportsWithPager($params);

in Template,

     <?php 
     $list = $pager->getResults(); 
     while($list->next()):
     ?> 
     <div><?php echo  $list->get('sum'); ?></div>  
     <?php endwhile; ?>

And creation page links same with sfPropelPager, read the sfPropelPager.

<?php
 
/*
 * For the full copyright and license information, please view the LICENSE
 * file that was distributed with this source code.
 *
 */
/**
 * @package    symfony
 * @subpackage addon
 * @author     Fabien Potencier <fabien.potencier@symfony-project.com>
 * @version    SVN: $Id: sfPropelPager.class.php 1415 2006-06-11 08:33:51Z fabien $
 */
 
/**
 *
 * myCustomPager class.
 *
 * @package    symfony
 * @subpackage addon
 * @author     Fabien Potencier <fabien.potencier@symfony-project.com>
 *   
 * Changed: sfPropelPager.class.php 
 * Changed by: Timu EREN
 * Last Changed: 12 / 08 /2006(dd /mm/ YYYY)
 */
class myCustomPager
{
  private
    $page                   = 1,
    $maxPerPage             = 0,
    $lastPage               = 1,
    $nbResults              = 0,
    $query               = null,
    $countQuery     = null,
    $objects                = null,
    $cursor                 = 1,
    $parameters             = array(),
    $currentMaxLink         = 1;
 
 
  public function __construct($cSQL,$SQL, $defaultMaxPerPage = 10)
  {
        $con = Propel::getConnection();
        $SQL = $con->prepareStatement($SQL);
        $cSQL = $con->prepareStatement($cSQL);
        $this->setQuery($SQL);
        $this->setCountQuery($cSQL);
        $this->setMaxPerPage($defaultMaxPerPage);
        $this->setPage(1);
  }
 
  public function init()
  {
        $cForCount =  $this->getCountQuery();
        $c = $cForCount->executeQuery();
        $c->next();
 
        $this->setNbResults($c->get('count'));
 
        $c =  $this->getQuery();
        $c->setLimit(0);
        $c->setOffset(0);
        if (($this->getPage() == 0 || $this->getMaxPerPage() == 0))
        {
            $this->setLastPage(0);
        }
        else
        {
            $this->setLastPage(ceil($this->getNbResults() / $this->getMaxPerPage()));
            $c->setOffset(($this->getPage() - 1) * $this->getMaxPerPage());
            $c->setLimit($this->getMaxPerPage());
        }
  }
 
  public function getCurrentMaxLink()
  {
        return $this->currentMaxLink;
  }
 
  public function getLinks($nb_links = 5)
  {
    $links = array();
    $tmp   = $this->page - floor($nb_links / 2);
    $check = $this->lastPage - $nb_links + 1;
    $limit = ($check > 0) ? $check : 1;
    $begin = ($tmp > 0) ? (($tmp > $limit) ? $limit : $tmp) : 1;
 
    $i = $begin;
    while (($i < $begin + $nb_links) && ($i <= $this->lastPage))
    {
      $links[] = $i++;
    }
 
    $this->currentMaxLink = $links[count($links) - 1];
 
    return $links;
  }
  public function haveToPaginate()
  {
    return (($this->getPage() != 0) && ($this->getNbResults() > $this->getMaxPerPage()));
  }
 
  public function getCursor()
  {
    return $this->cursor;
  }
 
  public function setCursor($pos)
  {
    if ($pos < 1)
    {
      $this->cursor = 1;
    }
    else if ($pos > $this->nbResults)
    {
      $this->cursor = $this->nbResults;
    }
    else
    {
      $this->cursor = $pos;
    }
  }
 
  public function getObjectByCursor($pos)
  {
    $this->setCursor($pos);
 
    return $this->getCurrent();
  }
 
  public function getCurrent()
  {
    return $this->retrieveObject($this->cursor);
  }
 
  public function getNext()
  {
    if (($this->cursor + 1) > $this->nbResults)
    {
      return null;
    }
    else
    {
      return $this->retrieveObject($this->cursor + 1);
    }
  }
 
  public function getPrevious()
  {
    if (($this->cursor - 1) < 1)
    {
      return null;
    }
    else
    {
      return $this->retrieveObject($this->cursor - 1);
    }
  }
 
  private function retrieveObject($offset)
  {
    $cForRetrieve = $this->getQuery();
    $cForRetrieve->setOffset($offset - 1);
    $cForRetrieve->setLimit(1);
    $results = $cForRetrieve->executeQuery();
    return $results;
  }
 
  public function getResults()
  {
    $c = $this->getQuery();
    return $c->executeQuery();
  }
 
  public function getFirstIndice()
  {
    if ($this->page == 0)
    {
      return 1;
    }
    else
    {
      return ($this->page - 1) * $this->maxPerPage + 1;
    }
  }
 
  public function getLastIndice()
  {
    if ($this->page == 0)
    {
      return $this->nbResults;
    }
    else
    {
      if (($this->page * $this->maxPerPage) >= $this->nbResults)
      {
        return $this->nbResults;
      }
      else
      {
        return ($this->page * $this->maxPerPage);
      }
    }
  }
 
  public function getQuery()
  {
    return $this->query;
  }
 
  public function setQuery($c)
  {
    $this->query = $c;
  }
 
  public function getCountQuery()
  {
    return $this->countQuery;
  }
 
  public function setCountQuery($c)
  {
    $this->countQuery = $c;
  }
 
  public function getNbResults()
  {
    return $this->nbResults;
  }
 
  private function setNbResults($nb)
  {
    $this->nbResults = $nb;
  }
 
  public function getFirstPage()
  {
    return 1;
  }
 
  public function getLastPage()
  {
    return $this->lastPage;
  }
 
  private function setLastPage($page)
  {
    $this->lastPage = $page;
    if ($this->getPage() > $page)
    {
      $this->setPage($page);
    }
  }
 
  public function getPage()
  {
    return $this->page;
  }
 
  public function getNextPage()
  {
    return min($this->getPage() + 1, $this->getLastPage());
  }
 
  public function getPreviousPage()
  {
    return max($this->getPage() - 1, $this->getFirstPage());
  }
 
  public function setPage($page)
  {
    $page = intval($page);
 
    $this->page = ($page <= 0) ? 1 : $page;
  }
 
  public function getMaxPerPage()
  {
    return $this->maxPerPage;
  }
 
  public function setMaxPerPage($max)
  {
    if ($max > 0)
    {
      $this->maxPerPage = $max;
      if ($this->page == 0)
      {
        $this->page = 1;
      }
    }
    else if ($max == 0)
    {
      $this->maxPerPage = 0;
      $this->page = 0;
    }
    else
    {
      $this->maxPerPage = 1;
      if ($this->page == 0)
      {
        $this->page = 1;
      }
    }
  }
}
 
?>

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
}