Snippets
Create an account or login to be able to add, comment and rate snippets.

Navigation

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 

Comments on this snippet

gravatar icon
#1 noel on 2007-02-21 at 12:49

This thing really shines when you want to do some complex subquerying or joins and you want bits of data from one table or another:

Check out this watchlist logic:

public function executeShowWatchlist() { $query = " FROM " . WatchlistPeer::TABLE_NAME . " as w WHERE user_id='" . $this->getUser()->getUserId() . "'";

$query_select = &quot;SELECT w.id, w.type, w.masterid, w.user_id, w.timestamp, w.is_updated,
                   (SELECT a.name FROM &quot; . ActivityPeer::TABLE_NAME . &quot; as a
                    WHERE w.masterid=a.masterid
                    AND a.type=w.type
                    LIMIT 1) as name&quot; . $query .
                &quot; ORDER BY is_updated DESC, timestamp&quot;;
 
$query_count = &#039;SELECT count(*)&#039; . $query;
 
$this-&gt;pager = new nonHydratingPager($query_select, $query_count, $this-&gt;getRequestParameter(&#039;page&#039;, 1), 100);
 
$rs = $this-&gt;pager-&gt;getResultSet();
$this-&gt;arrWatchlist = array();
 
while ( $rs-&gt;next() ) {
  $this-&gt;arrWatchlist[] = array(&#039;id&#039;=&gt;$rs-&gt;getString(1),
                                &#039;type&#039;=&gt;$rs-&gt;getString(2),
                                &#039;masterid&#039;=&gt;$rs-&gt;getString(3),
                                &#039;user_id&#039;=&gt;$rs-&gt;getString(4),
                                &#039;timestamp&#039;=&gt;$rs-&gt;getString(5),
                                &#039;is_updated&#039;=&gt;$rs-&gt;getString(6),
                                &#039;name&#039;=&gt;$rs-&gt;getString(7)
                             );
}

}

gravatar icon
#2 noel on 2007-02-21 at 12:50

bah comments need to be editable here

  public function executeShowWatchlist()
  {
    $query = " FROM " . WatchlistPeer::TABLE_NAME . " as w
               WHERE user_id='" . $this->getUser()->getUserId() . "'";
 
    $query_select = "SELECT w.id, w.type, w.masterid, w.user_id, w.timestamp, w.is_updated,
                       (SELECT a.name FROM " . ActivityPeer::TABLE_NAME . " as a
                        WHERE w.masterid=a.masterid
                        AND a.type=w.type
                        LIMIT 1) as name" . $query .
                    " ORDER BY is_updated DESC, timestamp";
 
    $query_count = 'SELECT count(*)' . $query;
 
    $this->pager = new nonHydratingPager($query_select, $query_count, $this->getRequestParameter('page', 1), 100);
 
    $rs = $this->pager->getResultSet();
    $this->arrWatchlist = array();
 
    while ( $rs->next() ) {
      $this->arrWatchlist[] = array('id'=>$rs->getString(1),
                                    'type'=>$rs->getString(2),
                                    'masterid'=>$rs->getString(3),
                                    'user_id'=>$rs->getString(4),
                                    'timestamp'=>$rs->getString(5),
                                    'is_updated'=>$rs->getString(6),
                                    'name'=>$rs->getString(7)
                                 );
    }
  }
gravatar icon
#3 Olivier Verdier on 2007-02-21 at 05:23

Your class should inherit the sfPager class which implements 90% of that snippet. Copy and paste and existing class to modify it a bit is never a good idea. Better to inherit from it.

gravatar icon
#4 noel on 2007-02-22 at 12:32

Yes you're absolutely right. For some reason the obvious wasn't apparent.

You need to create an account or log in to post a comment or rate this snippet.