Enabling pagination with raw SQL queries

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

  1. Kevin McGlynn

    brilliant… am taking this and running with it. This will help me get around Doctrine’s missing offset() implementation in SQL Server.

  2. crystal

    hihi
    i have tried your example. when i try to use my own table, i found that the code cannot work with the group by.

    below code cannot work, nothing is displayed:
    $connection = Propel::getConnection(WinLoseResultPeer::DATABASE_NAME, Propel::CONNECTION_READ);
    $query = ‘SELECT COUNT(l.id) AS cnt, c.member_id AS custom_column
    FROM tb_winlose_result c
    left join tb_winlose_result l ON l.member_id=c.member_id
    GROUP BY c.member_id';

    $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();

    But if i remove the count and group by, then the page will display the result, any idea why?

    The code that can display result:
    $connection = Propel::getConnection(WinLoseResultPeer::DATABASE_NAME, Propel::CONNECTION_READ);
    $query = ‘SELECT c.member_id AS custom_column
    FROM tb_winlose_result c
    left join tb_winlose_result l ON l.member_id=c.member_id
    ‘;

    $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 template i use is as below:
    getResults() as $resultset): ?>
    custom_column ?>

    Any help is really appreciated, thanks.

  3. Kyril

    Thanks for this. I modified your code a little to simulate the behavior of sfPropelPager, and return all results when passing ‘0’ as maxPerPage:

    // getResults

    while ($resultset = $this->statement->fetch($fetch_type))
    {
    if($this->getMaxPerPage() == 0){ // Return all results
    $this->resultsetArray[] = $resultset;
    }
    else{
    if ($row_num > $this->getMaxPerPage()*($this->getPage()-1)
    and $row_num getPage()*$this->getMaxPerPage() ))
    {
    $this->resultsetArray[] = $resultset;
    }
    }

    $row_num++;
    }

  4. deva

    Really super

  5. Kyril

    I noticed a subtle bug when using this pager (thanks again, btw)

    When retrieving results for the first page it will always skip the first record, since $row_num is set to 0, and will always fail the first conditional test

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

    I worked around this by setting $row_num to 1 instead

  6. Hi!

    I think there is still a bug in the code. if I execute the SQL directly in the db, I get 13 rows. echoing “$this->statement->rowCount();” at the beginning of “getResults()” returns 13. But adding this code

    while ($resultset = $this->statement->fetch(PDO::FETCH_OBJ)){
    echo “[".$resultset->title."]“;
    ….

    just returns 12 titles. My idea is the statement doesn’t stand on the first entry when entering the loop, but I am not sure at the moment.

    Michael

  7. Hi!

    I don’t find a solution. My work around is a union all to get a dummy line as the firt entry, which is not shown.

    This is really bad practice!

    I think we need help from somebody knowing pdo better than me.

    michael

  8. just stopping to show respect :) thanks!

  9. John La

    Thanks! Great Help!!!

  10. “just returns 12 titles. My idea is the statement doesn’t stand on the first entry when entering the loop, but I am not sure at the moment.”

    I solved my problem by adding $this->statement->execute() to the function getResults();

    Looks like that when you call the init() function a $this->statement->execute() is called and the first record loaded up in memory.

    Later, when you call ->fetchObject() the code jumps to the second record.

    I am leaving the code below, hope that it helps.

    public function getResults()
    {

    $this->statement->execute();
    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->fetchObject())
    {
    if ($row_num > $this->getMaxPerPage()*($this->getPage()-1)
    and $row_num getPage()*$this->getMaxPerPage() ))
    {
    $this->resultsetArray[] = $resultset;
    }
    $row_num++;
    }

    return $this->resultsetArray;
    }

  11. Defaka

    If you are using propel 1.4, your can try http://www.symfony-project.org/plugins/sfPropelCustomSelectPlugin, with this plugin you can select arbitrary columns and it’s work well with sfPropelPager.

    • nicolas.martin

      Well, I’ve finally switched to Doctrine now, but I’ll definitely give a try to Propel 1.4 next time I do a Propel-based project.
      This version sounds really great, especially the new inheritance features and query object !

  12. David

    When I call

    $this->pager->init();

    this warning appears:

    Warning: Invalid argument supplied for foreach() in D:\symfonyprojects\nameproject\lib\vendor\symfony\lib\plugins\sfDoctrinePlugin\lib\database\sfDoctrineConnectionProfiler.class.php on line 196

    Warning: join() [function.join]: Invalid arguments passed in D:\symfonyprojects\nameproject\lib\vendor\symfony\lib\plugins\sfDoctrinePlugin\lib\database\sfDoctrineConnectionProfiler.class.php on line 141

    Maybe is related to this bug?

    http://trac.symfony-project.org/query?component=sfDoctrinePlugin&verbose=1&milestone=1.3.9

    My system settings:
    php: 5.3.0
    sf version: 1.4.3

    Any suggestion?

    Thanks in advance.

  13. Phil

    If you have the same warning as David use this (for Doctrine)

    $connection = Doctrine_Manager::getInstance()->getCurrentConnection()->getDbh();

  1. 1 Closer To The Ideal » Blog Archive » Pagination for results when you've written a raw SQL query in Symfony

    [...] This would have been very handy, had I known it then: Whatever you use, Propel or Doctrine, it is sometimes needed to write raw SQL queries. [...]




Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s



Follow

Get every new post delivered to your Inbox.

%d bloggers like this: