Code snippets for symfony 1.x

Navigation

Refine Tags

Snippets tagged "pager propel pagination"

Minimal CSS pagination helper

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

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) ?>
by brikou on 2006-07-19, tagged css  helper  pager  pagination  propel 
(6 comments)

Pagination navigation helper

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') ?>
by Francois Zaninotto on 2006-05-20, tagged helper  pager  pagination  propel 

sfAdvancedPropelPager

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;
  }
}
 
?>
by Nick Winfield on 2006-12-01, tagged pager  pagination  propel 
(1 comment)

How to get a propel pager working with a custom SQL query

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
}
by Olivier Verdier on 2006-05-25, tagged custom  pager  pagination  propel  query  raw  sql 

nonHydratingPager class (a pager for when you dont want to hydrate your propel object)

<?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) {}
 
}
by noel on 2007-02-21, tagged nohydrate  nonhydrating  nonhydratingpager  pager  pagination  propel 
(4 comments)