Enabling pagination with raw SQL queries

February 2, 2009 in Arpeggios
Tags: Doctrine, pager, propel

sfPager is an abstract class that lets you deal with pagination at a high level. The symfony core comes with two concrete implementations of sfPager : sfPropelPager and sfDoctrinePager (one for each ORM).

The benefit of this abstraction is that you can reuse the business-logic of pagination and apply it to anything, without reinventing the wheel.

Scott Meves have already submitted a snippet in which he explains how to extend the sfPager class to paginate an array.

Raw SQL

Whatever you use Propel or Doctrine, it is sometimes needed to write raw SQL queries.

$connection = Propel::getConnection();
$query = 'SELECT MAX(%s) AS max FROM %s';
$query = sprintf($query, ArticlePeer::CREATED_AT, ArticlePeer::TABLE_NAME);
$statement = $connection->prepare($query);
$statement->execute();
$resultset = $statement->fetch(PDO::FETCH_OBJ);
$max = $resultset->max;

When you do so, you loose all the relation object mapping. Besides, you cannot use any of the sf*Pager anymore.

Nevertheless, both Propel and Doctrine are based on PDO. It means that using raw queries doesn’t mean bypassing database abstraction. When you execute a query, you still retrieve a collection of PDOResulset objects, which are ‘abstract’ representations of database entries.

the statementPager class

Let’s create a custom pager to paginate this collection (an array of objects) :

class statementPager extends sfPager
{
  protected $resultsetArray = array();

  public function __construct($class = null, $maxPerPage = 10)
  {
    parent::__construct($class, $maxPerPage);
  }

  public function setStatement($statement)
  {
    $this->statement = $statement;
  }

  public function init()
  {
    $this->statement->execute();

    $this->setNbResults($this->statement->rowCount());

    if (($this->getPage() == 0 || $this->getMaxPerPage() == 0))
    {
     $this->setLastPage(0);
    }
    else
    {
     $this->setLastPage(ceil($this->getNbResults() / $this->getMaxPerPage()));
    }
  }

  public function getResults()
  {

    if (($this->getPage() == 0 || $this->getMaxPerPage() == 0))
    {
      $this->setLastPage(0);
    }
    else
    {
      $this->setLastPage(ceil($this->getNbResults() / $this->getMaxPerPage()));
    }

    $row_num = 1;
    while ($resultset = $this->statement->fetch(PDO::FETCH_OBJ))
    {
      if ($row_num > $this->getMaxPerPage()*($this->getPage()-1)
      and $row_num <= ($this->getPage()*$this->getMaxPerPage() ))
      {
        $this->resultsetArray[] = $resultset;
      }
      $row_num++;
    }

    return $this->resultsetArray;
  }

  public function retrieveObject($offset)
  {
    return $this->resultsetArray[$offset];

  }
}

To avoid populating a big array with all resultsets (which is the current limitation of the snippet), we only stack those that match with the current page range ( simulating the limit/offset behavior of a SQL query). This is not an optimal solution, but it really helps keeping memory usage low since the $resultset variable is overwritten on each pass.

To initialize the pager, simply pass a PDOStatement instance to the pager after preparing the query :

$connection = Propel::getConnection(CampainPeer::DATABASE_NAME, Propel::CONNECTION_READ);
$query = 'SELECT COUNT(l.id) AS cnt, column AS custom_column
FROM table c
left join another_table l ON l.id=c.another_id
GROUP BY c.name
order by l.created_at DESC';

$this->statement = $connection->prepare($query);

$this->pager = new statementPager(null, 10);
$this->pager->setStatement($this->statement);
$this->pager->setPage($request->getParameter('page', 1));
$this->pager->init();

The pager is now initialized and can be used just like any pager :

<?php foreach ($pager->getResults() as $resultset): ?>
  <?php print_r($resultset) ?>
<?php endforeach ?>

Will output 10 resultsets :

stdClass Object
(
  [cnt] => 1
  [custom_column] => 526
)
stdClass Object
(
  [cnt] => 3
  [custom_column] => 527
)
stdClass Object
(
  [cnt] => 0
  [custom_column] => 123
)
stdClass Object
(
  [cnt] => 0
  [custom_column] => 187
)
stdClass Object
(
  [cnt] => 1
  [custom_column] => 109
)
stdClass Object
(
  [cnt] => 1
  [custom_column] => 526
)
stdClass Object
(
  [cnt] => 3
  [custom_column] => 527
)
stdClass Object
(
  [cnt] => 3444
  [custom_column] => 456
)
stdClass Object
(
  [cnt] => 110
  [custom_column] => 789
)
stdClass Object
(
  [cnt] => 220
  [custom_column] => 101
)

About these ads