Pagination with PHP Doctrine
Things are starting to become more and more feature complete. Let’s look at how to implement general search and pagination. In the Product model:
public $searchConf = array(
'start_price' => array('where' => 'this.price >= ?', 'value' => '?'),
'end_price' => array('where' => 'this.price <= ?', 'value' => '?')
);
public $pageConf = array('per_page' => 2, 'per_chunk' => 3, 'page_style' => 'Sliding');
So we’ve got two things here, $searchConf and $pageConf and you guessed it. The former will affect the search behavior and the latter the pagination.
The logic we will get to in a moment will check if each key in the $_POST array exists in $searchConf, if they do the information there will be used, if not a default behavior is initialized.
The $pageConf variable is necessary though, I haven’t bothered with any default values here, per_page controls how many items we show on each page, per_chunk controls how many pages we have at one time in the pagination controls and page_style controls the behavior of the pagination controls.
Currently Doctrine offers Sliding and Jumping. See the manual under Utilities->Pagination->Controlling range styles for more information on how these two modes are different.
function paginate(){
$this->obj = $this->myMdl();
if($this->g['start'] == 1)
$this->setS('query', '');
$cur_page = empty($this->g['page']) ? 1 : $this->g['page'];
$query = $this->getS('query');
if(empty($query)){
$query = $this->search(NULL, true);
$this->setS('query', $query);
}
$pager = new Doctrine_Pager($query, $cur_page, $this->obj->pageConf['per_page']);
$pager_range = $pager->getRange($this->obj->pageConf['page_style'], array('chunk' => $this->obj->pageConf['per_chunk']));
$pager_layout = new Doctrine_Pager_Layout($pager, $pager_range, $this->base_dir.'paginate/page/{%page_number}');
$pager_layout->setTemplate('[<a href="{%url}">{%page}</a>]');
$pager_layout->setSelectedTemplate('[{%page}]');
$this->assign('items', $pager->execute()->toArray());
$this->assign('pagination', $pager_layout->display(array(), true));
return $this->fetch('search_result.tpl');
}The above is from the Ctrl class. If we have a start key of one in the $_GET array we take it to mean that the search should be reseted by setting the query object in the session to the null string.
Similarly, if we don’t have a value under the page key we will assume it’s the first search we make and will therefore load the first page.
If the query in the session is empty (i.e. if we’re dealing with a fresh search) we will create a new query object and put it in the session.
We create the pager object with our query object, the current page number, and the number of items we will have per page.
We also need a page range object which we will use to create the pager layout object, note the last argument, this is the url that will be used to jump between pages in the pagination controls.
We set the look of each page we are currently not on with setTemplate(), the page we are currently on gets its look from setSelectedTemplate().
Finally we assign the items and the pagination control HTML to use in the Smarty template. Note the last argument in the call to $pager_layout->display, if it is omitted or false we will echo the HTML of the controls instead of fetching them as a string, not what we want here.
...
session_start();
$_SESSION['lang'] = empty($_SESSION['lang']) ? 'en' : $_SESSION['lang'];
Common::index();Since we are storing the query object in the session we need to move the call to session_start() way down almost at the bottom of index.php to avoid loading classes after it is called.
function search($post = NULL, $as_query = false){
$p = empty($post) ? $this->p : $post;
$p = Arr::fluent($p)->trim()->c;
$dql = '';
$arr = array();
$first = true;
foreach($p as $key => $value){
$dql .= $first ? '' : ' AND ';
if(array_key_exists($key, $this->obj->searchConf)){
$dql .= $this->obj->searchConf[$key]['where'];
$arr[] = str_replace('?', $value, $this->obj->searchConf[$key]['value']);
}else{
$type = Arr::fluent($this->t->getDefinitionOf($key))->at('type');
switch($type){
case 'string':
$tmp = ' LIKE ?';
$arr[] = "%$value%";
break;
case 'timestamp':
$tmp = ' > ?';
$arr[] = $value;
break;
default:
$tmp = ' = ?';
$arr[] = $value;
break;
}
$dql .= "this.$key".$tmp;
}
$first = false;
}
if(!empty($arr)){
$query = $this->from()->where($dql, $arr);
return $as_query ? $query : $query->execute()->toArray();
}else
return $as_query ? $this->t->createQuery() : $this->findAllArr();
}The goal here is to build a proper DQL string to use in the call to where(). We begin with getting rid of any zero or non existent entries with the call to Arr::trim(). The rest is a loop of what’s left, if there is a value for the given key in the searchConf array in the model object we use the information there. If not we use the fact that getDefinitionOf() will return the type of the given field as it was assigned in the model with hasColumn().
If we have a string we will default to LIKE %something% to get any kind of substring. In case of a timestamp we fetch all entries newer than the selected date which needs to have the yyyy-mm-dd form for this to work. Se the jQuery datePicker tutorial for more info on how this can be accomplished in an easy way.
Finally per default, such as when we are dealing with an integer, we simply make a straight comparison.
If the search form was simply submitted we will act as if that means we should retrieve all items in the table with Doctrine_Table::createQuery() if a query object is the desired return value. If not we simply call findAllArr() to get everything as an array instead.
And finally the HTML in search_result.tpl:
{foreach from=$items item=i}
{$i.name}<br/>
{/foreach}
<br/>
{$pagination}The source has been updated.
Update 5 September 2009:
It seems I was a little too fast above, when I actually tested this stuff I realized that the query object wasn’t unserializing from the session in a proper way. At the moment I don’t really have time to make this work, if it is even possible, there might be complicated database dependencies etc that won’t allow it.
We will instead store the DQL and the array we use with it in the session and recreate the query object on each call to paginate.
The search method now looks like this instead:
function search($post = NULL, $as_query = false){
list($dql, $arr) = $this->getWhere($post);
if(!empty($arr)){
$query = $this->from()->where($dql, $arr);
return $as_query ? $query : $query->execute()->toArray();
}else
return $as_query ? $this->t->createQuery() : $this->findAllArr();
}The DQL is now being built by the getWhere() method instead, reason being that we want to use it not just in the search method.
function getWhere($post = NULL){
$this->setWhere();
$dql = $this->getS('dql');
if(!empty($dql))
return array($dql, $this->getS('dql_arr'));
$p = empty($post) ? $this->p : $post;
$p = Arr::fluent($p)->trim()->c;
$dql = '';
$arr = array();
$first = true;
foreach($p as $key => $value){
$dql .= $first ? '' : ' AND ';
if(array_key_exists($key, $this->obj->searchConf)){
$dql .= $this->obj->searchConf[$key]['where'];
$arr[] = str_replace('?', $value, $this->obj->searchConf[$key]['value']);
}else{
$type = Arr::fluent($this->t->getDefinitionOf($key))->at('type');
switch($type){
case 'string':
$tmp = ' LIKE ?';
$arr[] = "%$value%";
break;
case 'timestamp':
$tmp = ' > ?';
$arr[] = $value;
break;
default:
$tmp = ' = ?';
$arr[] = $value;
break;
}
$dql .= "this.$key".$tmp;
}
$first = false;
}
$this->setWhere($dql, $arr, false);
return array($dql, $arr);
}
function setWhere($dql = '', $arr = array(), $on_start = true){
if($on_start && $this->g['start'] != 1)
return;
$this->setS('dql', $dql);
$this->setS('dql_arr', $arr);
}The logic is almost identical to the earlier query checking, setting and getting logic but now we work with the string and array instead.
function paginate($query = false, $tpl = 'search_result.tpl'){
$this->obj = $this->myMdl();
$cur_page = empty($this->g['page']) ? 1 : $this->g['page'];
$query = $query == false ? $this->search(NULL, true) : $query;
$pager = new Doctrine_Pager($query, $cur_page, $this->obj->pageConf['per_page']);
$pager_range = $pager->getRange($this->obj->pageConf['page_style'], array('chunk' => $this->obj->pageConf['per_chunk']));
$pager_layout = new Doctrine_Pager_Layout($pager, $pager_range, $this->base_dir.'paginate/page/{%page_number}');
$pager_layout->setTemplate('[<a href="{%url}">{%page}</a>]');
$pager_layout->setSelectedTemplate('[{%page}]');
$this->assign('items', $pager->execute(array(), Doctrine::HYDRATE_ARRAY));
$this->assign('pagination', $pager_layout->display(array(), true));
return $this->fetch($tpl);
}The above convention with /start/1 lets us reset the DQL each time an original search is being carried out. Since subsequent calls to paginate won’t contain this key/value we simply use the DQL that was created in the original search.
Note also the use of Doctrine::HYDRATE_ARRAY in the call to execute(). We used execute()->toArray() earlier, this will generate results different from ->fetchArray() which is what we really want. However the Doctrine pager object doesn’t have fetchArray(). Luckliy fetchArray() is simply an alias for passing the above arguments to execute().
Related Posts
Tags: Doctrine, pagination, PHP, search, Smarty