How to paginate a CakePHP search over a HABTM relation without hacking the core
Posted on 21/11/08 by Tim Koschützki
Hey folks,
this post is going to deal with the pretty common problem of paginating a search with the CakePHP framework.
The Problem
The problem is that a user inputs some search criteria into a form, the resultset exceeds 30 rows for example and the user must be able to browse through the resultset via different pages. The search criteria has to be the same everytime, the form prepopulated with the used search keywords/data and the resultset still has to match the input conditions everytime a new page is clicked.
This problem itself is in fact not much of a problem. We just need to store the form conditions somewhere and then hack it together. So what we are going to do is that we raise the difficulty bar a lot more by trying to get the pagination work over a HABTM relation.
Battlefield Briefing
We agreed we need to store the search criteria somewhere, so we can access it later. No we won't use the DB for that as it is overkill. We will also not use files, since many users may use the search at the same time screwing our hd. :P Yes, we will use the session for that as it is made exactly for these things.
For the example code we are going to use the "Advanced Search" of Flashfun247, a flashgame site which is one of my freetime projects. If you want to see some more of its code, feel free to ask.
We want to search for games based on some input conditions. The Game model is related to the GameCategory model over a HABTM relation, so the same game can be in many categories and a category contains many games. CakePHP's paginator cannot handle pagination over a HABTM so well in its current version. The incident here is that we want every game listed only once in the resultset - and not n times, where n is the number of categories it belongs to.
So we must at some point include a group by statement. However, the paginator will use that group by statement for its internal find('count') call as well, which it does to determine the size of the resultset. This will in fact corrupt the page count screwing us all over. We will see that we can trick the paginator, though. ; )
The View
To get us started, let's have a look at the view in /views/searches/advanced.ctp, which is very simple:
<?php
$html->addCrumb($this->pageTitle);
echo $form->create('Search', array('action' => 'advanced'));
if (isset($formData) && !empty($formData)) {
$form->data = $formData;
}
echo $form->input('game_category_id', array('label' => 'Category:', 'options' => $searchCategories, 'empty' => 'All Categories'));
echo $form->input('keywords', array('label' => 'Text from game name, description or instructions:'));
echo $form->input('tags', array('label' => 'Is tagged with (separate tags by comma):'));
$orderOptions = array(
'Game.name' => 'Name',
'GameCategory.name' => 'Game Category',
'Game.avg_rating' => 'Game Rating',
'Game.clicks' => 'Number of Plays',
);
echo $form->input('order_by', array('label' => 'Order Results By:', 'options' => $orderOptions));
echo $form->input('order_dir', array('label' => 'Direction:', 'options' => array('asc' => 'Ascending', 'desc' => 'Descending')));
?>
<div class="clear"></div>
<?php echo $form->end('Search', array('action' => 'search'))?>
<?php if (isset($games)) : ?>
<div class="clear"></div>
<?php if (!empty($games)) : ?>
<?php echo $this->element('../games/list', array('games' => $games, 'hilite' => $query))?>
<div class="clear"></div>
<?php echo $this->element('paging', array('model' => 'GameCategoriesGame'))?>
<?php else : ?>
<p class="error-message">Sorry, your search returned no results</p>
<?php endif; ?>
<?php endif; ?>
It should be pretty straightforward. The only weird thing here is that $formData array. It is basically the placeholder for our search criteria that the user originally typed into the search form field. The view only needs to know where the form data is and not where it comes from. We simply assign the data to the form helper so it can prepopulate the fields for us (line 7).
The user can input here a substring of the name/description/instructions of a game and he can pick a category where the game must be in. Notice the different order options as well as the string "All Categories" for the empty option of the select tag. One other remarkable thing is that we have both isset($games) and !empty($games) calls there. This is to differentiate if the user has submitted the form already ( isset($games) ) and, if he did, the resultset is not empty which allows us to display that "Nothing found" message.
Here is the /views/games/list.ctp view just so you have the complete code:
$short = isset($short) ? $short : false;
$class = $short ? ' short' : '';
?>
<div class="games-list">
<?php foreach ($games as $game) : ?>
<div class="game<?php echo $class ?>">
<div class="game-image">
<?php echo $this->element('game_image', array('game' => $game, 'thumb' => true))?>
</div>
<div class="game-descr">
<?php
$name = $game['Game']['name'];
if (isset($hilite)) {
$name = $text->highlight($game['Game']['name'], $hilite);
}
echo $html->link($name, Game::url($game), null, false, false);
?>
<?php if (!$short) : ?>
<?php echo $game['Game']['short_desc'] ?>
<div class="plays"><span>Plays:</span> <?php echo $game['Game']['game_playing_count']?></div>
<?php endif; ?>
</div>
</div>
<?php endforeach; ?>
</div>
?>
Straightforward... Let's move on to the paging element:
if (!isset($model) || $paginator->params['paging'][$model]['pageCount'] > 1) : ?>
<div class="paging">
<?php echo $paginator->prev('« Previous', array('escape' => false, 'class' => 'prev'), null, array('class'=>'disabled'));?>
<?php echo $paginator->numbers();?>
<?php echo $paginator->next('Next »', array('escape' => false, 'class' => 'next'), null, array('class'=>'disabled'));?>
</div>
<?php endif; ?>
Notice the different checks at the start in order to figure out if we need to display a div at all.. This is called in the advanced.ctp view and the model GameCategoriesGame is supplied, which is a convenience HABTM model which belongsTo both Game and GameCategory.
The controller action
The controller action might appear a little big at first glance. However, every line has its purpose. This is in a SearchesController. You could have your own search() method though in about any controller.
$searchCategories = $this->Game->GameCategory->find('list', compact('conditions'));
$this->set(compact('searchCategories'));
$page = 1;
if (isset($this->params['named']['page'])) {
$page = $this->params['named']['page'];
}
$formData = array();
$sessionKey = 'advanced_search_query';
if (isset($this->data['Search']['keywords'])) {
$formData = $this->data;
$this->Session->write($sessionKey, $formData);
} elseif ($this->Session->check($sessionKey)) {
$formData = $this->Session->read($sessionKey);
} else {
Assert::true(false, '404');
}
$this->set(compact('formData'));
if (!empty($formData)) {
$query = $formData['Search']['keywords'];
$useQuery = trim(low($query));
$conditions = array();
if (!empty($formData['Search']['game_category_id'])) {
$conditions['GameCategoriesGame.game_category_id'] = $formData['Search']['game_category_id'];
}
$conditions = am($conditions, array(
'Game.published' => '1',
'or' => array(
'LOWER(Game.name) LIKE' => "%{$useQuery}%",
'LOWER(Game.short_desc) LIKE' => "%{$useQuery}%",
'LOWER(Game.long_desc) LIKE' => "%{$useQuery}%",
'LOWER(Game.instructions) LIKE' => "%{$useQuery}%"
)
));
$this->GameCategoriesGame->forcePaginateCount = $this->GameCategoriesGame->paginatorCount(
'game_categories_games', $conditions, array('Game')
);
$contain = array('GameCategory', 'Game.Tag');
$order = array('Game.name' => 'asc');
if (!empty($formData['Search']['order_by'])) {
$order = array($formData['Search']['order_by'] => $formData['Search']['order_dir']);
}
$this->paginate['GameCategoriesGame'] = array(
'conditions' => $this->GameCategoriesGame->paginatorConditions('game_categories_games', $conditions),
'contain' => $contain,
'order' => $order,
'limit' => 12
);
$games = $this->paginate('GameCategoriesGame');
$this->set(compact('games', 'query'));
}
}
So we are first loading all our game categories to populate the select tag. Then we check if there is a named parameter "page" given. If so, the user clicked on the Previous/Next/Numbered links. If it is not present, we might as well start at page 1. ; ]
Now comes the tricky part. We check if the form was submitted via empty($this->data). If it is submitted, we store all the form data in the session. If the form is not submitted we try to recover the form data from the session. If both the form is not submitted and there is no data in the session, but it still a Get request, something bad happened and we fire the user by asserting the yummyness of his cake.
The rest should be familiar - some processing of the $formData array to extract the proper conditions and order stuff. The most interesting stuff now is that call to $this->GameCategoriesGame->paginatorCount('game_categories_games', $conditions, array('Game'));. This enables us to paginate over the HABTM relation (Game HABTM GameCategory). Here is the code from the GameCategoriesGame model:
class GameCategoriesGame extends AppModel {
var $name = 'GameCategoriesGame';
var $belongsTo = array('GameCategory', 'Game');
/**
* Return count for given pagination
*
* @param string $paginator Pagination name
* @param array $conditions Conditions to use
* @return mixed Count, or false
* @access public
*/
function paginatorCount($paginator, $conditions = array(), $contain = array()) {
$Db = ConnectionManager::getDataSource($this->useDbConfig);
if (!empty($contain)) {
$related = ClassRegistry::init($contain[0]);
}
$sql = 'SELECT
COUNT(DISTINCT ' . $this->alias . '.' . $this->belongsTo['Game']['foreignKey'] . ') count
FROM ' . $Db->fullTableName($this->table) . ' ' . $Db->name($this->alias) . ' ';
if (!empty($contain)) {
$sql .= ' INNER JOIN ' . $Db->fullTableName($related->table) . ' ' . $Db->name($related->alias) . ' ';
}
$sql .= $Db->conditions($this->paginatorConditions($paginator, $conditions, 'count'));
$count = $this->query($sql);
if (!empty($count)) {
$count = $count[0][0]['count'];
}
return $count;
}
/**
* Build conditions for given pagination
*
* @param string $paginator Pagination name
* @param array $extraConditions Extra conditions to use
* @param string $method 'count', or 'find'
* @return array Conditions
* @access public
*/
function paginatorConditions($paginator, $extraConditions = array(), $method = null) {
$Db = ConnectionManager::getDataSource($this->useDbConfig);
$conditions = null;
if (empty($extraConditions)) {
$extraConditions = array('1=1');
}
switch (strtolower($paginator)) {
case 'game_categories_games':
if ($method != 'count') {
$conditions = array_merge($extraConditions, array('1=1 GROUP BY ' . $this->alias . '.' . $this->belongsTo['Game']['foreignKey']));
} else {
$conditions = $extraConditions;
}
break;
}
return $conditions;
}
/**
* Executed by the paginator to get the count. Overriden to allow
* forcing a count (through var $forcePaginateCount)
*
* @param array $conditions Conditions to use
* @param int $recursive Recursivity level
* @return int Count
* @access public
*/
function paginateCount($conditions, $recursive) {
if (isset($this->forcePaginateCount)) {
$count = $this->forcePaginateCount;
unset($this->forcePaginateCount);
} else {
$count = $this->find('count', compact('conditions', 'recursive'));
}
return $count;
}
}
?>
To make a long story short: You see we build up the count query on our on and then force Cake to use our calculated count via our own forcePaginateCount property of the model. The Group BY is already in there, we can supply extra conditions and have different queries for different types (see the switch statement in paginatorConditions).
Alas, we have to build the sql on our own for the JOINs, which can become a headache for more complex problems. Anyway, this code gives us enough flexibility to build the right pagination for every problem. :) If you can think of a problem this code cannot be used for, please let me know and we discuss.
The paginateCount() method could go into your AppModel, I just put it here to have the code in one place to keep it simpler.
Conclusion
The method presented has some advantages and disadvantages, as always. The advantages would definitely include that we don't have to extend the controller's paginate() method in our app controller. This is what many people do and what I did in the past as well. However, as always, it's not good manners to hack the core.
Another advantage is the flexibility of the code - with just one line, we can calculate pagination counts for almost every occasion, and even if we paginate over two or three HABTM relations (I can show you later).
Disadvantages include some bloat in your models and the need to write sql again (*sigh*), which can become very complex if you have to supply all the JOINS yourself for more complex problems. Apart from that the code does not yet have full integration of the containable behavior. However, that I can add later.
I hope you liked the article and can put it to some use. Credits go to mariano for the original idea for this. If you guys are interested in seeing how I coupled the "Save search" feature from here with all of this, feel free to ask and we can have some nice discussion.
Did you like this blog post? If so, please consider subscribing to the Blog RSS feed.
You can skip to the end and add a comment.
Michał Szajbe: Thanks for your comment. The approach should be pretty fast. After all, all what we do is calculating a COUNT with sql...
As for storing the search data in the session: Well what would be the alternative? Storing it all in the url, which will result in very cryptic and long urls. What would you suggest?
@Michał Szajbe "Storing search parameters in session - not the best solution I think. User may be doing different searches in two browser windows and get unexpected results."
This is true if the "two browser windows" are from the same browser application, not from eg. a firefox window with a safari one.
To me it seems that the search quarry should only be remembered through the URL. This is more efficient, more straight forward, and you are able to bookmark searches, and also Google can correctly index them. This means setting the search form type to GET (instead of POST). Then, when you click on "Next Page" or whatever, the GET string will be transferred over. Simple as heck.
For instance, if you are at "/games/search?str=war&dir=ASC&page=1", the next button would just point to "/games/search?str=war&dir=ASC&page=2".
You can argue the URLs are not pretty this way, but think about it, how many sites have pretty URLs for their search pages? Not Google! Think about why Google does not use sessions.
Sometimes you just gotta think about how you did things before you made love to cake...
In one my projects I searched products by selecting tags (product habtm tag). The URL looked something like /t/tag1,tag2,tag3/page:2. I used routes for this
Router::connect('/t/:tagSlugs/*', array('controller' => 'products', 'action' => 'index'));
Another problem of this method is: if a user search, then he try a game for a couple of minutes, then go back to the search page and click 'next page' but the session has been expired and return nothing.
Okay guys, I agreed already that the search should be supported by urls already.
However, the article is not only about the search - but the HABTM pagination. Any comments about that?
Hey Tim,
Thanks for the post. I worked on something similar to this a month or so back. I went with the solution of storing the data into the URL. I built it so that you can determine a url length threshold.
It takes care of the whole session issue. As for HABTM pagination, I went with the manual on this one and built custom find() queries.
The paginatorCounter is interesting... When I have more time I'll review it more in depth. Something along those lines may be an improvement to my code, however, for now I try to keep it simple and readable so that it is manageable to create new reports.
Dooltaz: Sounds cool to me. : ) Yeah I will implement the whole url stuff into my method. Not sure why I haven't thought about it beforehand. :]
COUNT(DISTINCT.. is evil evil evil!
othmane ouahbi: Please explain why it is evil in this context.
"If you can think of a problem this code cannot be used for, please let me know and we discuss."
Consider, that the Game model is also HABTM related to Players model. I want the Game results to be filtered on the conditions on Categories and Players models (eg. Game.Category = "RPG" AND Player.Score > 1000)
"$this->GameCategoriesGame->paginatorCount('game_categories_games', $conditions, array('Game'));"
Seeing that you're going through the GameCategories model for the first condition, it would not support conditions for the Players model (which is joined by games_players).
How would you support conditions like these (with pagination)? Would you consider it food for thought for another article?
Hrm not easy. This needs two queries in my opinion, because of the extra HABTM between categories and games.
What about a GamesPlayer model which is the HABTM model for the relation Player to Game. It belongsTo Player and Game.
In the first query you would fetch all ids of the games in the RPG category. Then you can do ap agination query as outlined in the article with a IN (array_of_game_ids) clause.
What do you think?
Unfortunately, in CakePHP it is not easy to express joins without writing custom SQL.
Other PHP frameworks have made good progress in solving these issues by adapting popular ORM layers (ActiveRecord in CodeIgniter, Doctrine in Symfony) whereas CakePHP still relies on users hacking/working around the limited database quering features.
I haven't worked with other PHP frameworks, but Django 1.0 (Python) makes it a snap to get data from multiple related models based on multiple conditions (across models) and automatically takes care of the joins:
http://docs.djangoproject.com/en/dev/topics/db/queries/
(look under the heading: Spanning multi-valued relationships)
tonedeaf: Actually, doing ad-hoc joins in CakePHP is quite easy. I've been meaning to do a post on it for a while, I'll have to do that soon.
Nate: "Actually, doing ad-hoc joins in CakePHP is quite easy. I've been meaning to do a post on it for a while, I'll have to do that soon."
I've spent countless hours searching for posts (bakery, google groups, teknoid etc.) and I see the questions repeated again and again.
What is of interest to me (and to other CakePHP users, I believe) is how to do a HABTM, Many-to-Many Search across multiple models. It is like the containable behaviour which allows for filtering of the results. Look at this ticket for details:
https://trac.cakephp.org/ticket/4712
Now, there is a workaround to filter the results on the teknoid website, but it is less than optimal and won't support big record sizes.
Can you please do a post on doing such database queries through CakePHP? I'm kind of frustated by CakePHP's limitations / lack of reference / my lack of knowledge and I'm actively considering switching on to Django. Your post will go a long way in restoring my confidence back in CakePHP
Thanks Nate for the article, I just found it today. If anyone else wants to look over it, here's the link: http://bakery.cakephp.org/articles/view/quick-tip-doing-ad-hoc-joins-in-model-find
I have to have one page that has 3 tables on it. Each of the tables needs to be paginated, and they are ALL from the SAME model. They are for Trades, so I'm having 3 tables, one will show all trades in state open, the other for all trades in state closed, and the final all in pending state.
My question is, how can I call the paginate 3 different times in the same model? I do:
$this->set('trades1', $this->paginate('Trade', array('Trade.trading_state_id'=>'open')));
$this->set('trades2', $this->paginate('Trade', array('Trade.trading_state_id'=>'closed')));
$this->set('trades3', $this->paginate('Trade', array('Trade.trading_state_id'=>'pending')));
Then when I render the screen, I use the appropriate data for the appropriate table. HOWEVER, all of the paging stats (next, previous, # of records) are all the data from the last paginate call of course...
I'm using AJAX to render only the appropriate section, although I don't know where the user is coming from, it is re-rendering the entire screen (all 3 tables) in the desired output div instead of just the one.
In summary, how can you call paginate multiple times for the same model in your controller? Also, is there a way of knowing where you came from (ie which table did you click next on) from your views?
Hope this makes sense. Any help would be appreciated.
Well what you can do is this:
1. Create two dummy models that extend the original Trade Model. They inherit all functionality. The thing is, that they need a different alias (which is achieved by their class name) in order to distuingish them in the pagination process.
2. Put this code in your app controller.
/**
* undocumented function
*
* @param string $model
* @return void
* @access public
*/
function pageForPagination($model) {
$page = 1;
$sameModel = isset($this->params['named']['model']) && $this->params['named']['model'] == $model;
$pageInUrl = isset($this->params['named']['page']);
if ($sameModel && $pageInUrl) {
$page = $this->params['named']['page'];
}
$this->passedArgs['page'] = $page;
return $page;
}
3. Make sure your paging urls have a 'model' setting, so that the model is appended as named paramter to the url.
4. In your pagination calls, make sure to add the attribute
'page' => $this->pageForPagination('Trade2')
Does this help?
Hi Tim,
Thanks for your feedback. I had implemented a solution similar to yours before there was a comment back. I created 'dummy' models, there was still issues with the 'remembering' what page and what sort parameters that were supposed to be saved after entering into an edit page and returning for example, so I handled these with Session variables for each model (not sure if this is really a good way of doing it). I just need a way of clearing the session variable when it is the first time that I come into the screen, other than that seems to work, now I just have to add a lot of them to one page and test that out :)
I will definitely try your pageForPagination method on my next screen that uses this to see if it is easier than what I've implemented. I do appreciate the feedback.
Tara
If you use the POST method in your search form, you could check for if ($this->RequestHandler->isPost()) to clear your session variable, perhaps?
@Nate Abele: Have you had a chance to discuss the ad-hoc join simplicity in Cake? I'd be interested to see how you integrate this with pagination.
Montana Harkin: See the comment above from tonedeaf.
Ah, Yes, if only I were to read _all_ the comments. Thanks.
Nice article! I wish I could find it several months ago... I had the problem similar to one of the commentators - while searching with HABTM relation how to fetch the data from database without damaging it. I tried to find the solution in the internet. But neither ar forums nor at rapidshare ( http://rapidqueen.com ) managed to find. Now I have the solution, but unfortunatelly too late (((
The table in database table is missing,
and I don't think I get my table right...
+-------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| keywords | varchar(20) | YES | | NULL | |
| id | int(11) | NO | PRI | NULL | |
| name | varchar(30) | YES | | NULL | |
| body | text | YES | | NULL | |
| category_id | int(11) | YES | | NULL | |
| tag_id | int(11) | YES | | NULL | |
| list | varchar(30) | YES | | NULL | |
| tag | varchar(30) | YES | | NULL | |
+-------------+-------------+------+-----+---------+-------+
Notice (8): Undefined property: SearchesController::$Game [APP/controllers/searches_controller.php, line 8]
Code
function advanced() {
$searchCategories = $this->Game->GameCategory->find('list', compact('conditions'));
SearchesController::advanced() - APP/controllers/searches_controller.php, line 8
Object::dispatchMethod() - CORE/cake/libs/object.php, line 118
Dispatcher::_invoke() - CORE/cake/dispatcher.php, line 227
Dispatcher::dispatch() - CORE/cake/dispatcher.php, line 194
[main] - APP/webroot/index.php, line 88
Notice (8): Trying to get property of non-object [APP/controllers/searches_controller.php, line 8]
Code
function advanced() {
$searchCategories = $this->Game->GameCategory->find('list', compact('conditions'));
SearchesController::advanced() - APP/controllers/searches_controller.php, line 8
Object::dispatchMethod() - CORE/cake/libs/object.php, line 118
Dispatcher::_invoke() - CORE/cake/dispatcher.php, line 227
Dispatcher::dispatch() - CORE/cake/dispatcher.php, line 194
[main] - APP/webroot/index.php, line 88
Fatal error: Call to a member function find() on a non-object in /var/www/cake/app/controllers/searches_controller.php on line 8
Please tell me what's wrong.....
This is the website:
http://cs215.mooo.com:16080/cake/searches/advanced/
Sorry, wrong table
This is the right one:
+------------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------------+-------------+------+-----+---------+----------------+
| game_category_id | int(11) | YES | | NULL | |
| keywords | varchar(50) | YES | | NULL | |
| tags | varchar(50) | YES | | NULL | |
| id | int(10) | NO | PRI | NULL | auto_increment |
+------------------+-------------+------+-----+---------+----------------+
Did you make sure the game model is loaded in your $uses declaration in the SearchesController?
HI Tom,
Can you share script for this project so we could have better look and understand what exactly you make in that project.
Thanks for nice post.
Much appreciated !!
hey guys!
I am in real trouble, we are creating a system for risk assessment for a construction company. I am looking for a code for search, and risk matrix using cake php.
please help!
This post is too old. We do not allow comments here anymore in order to fight spam. If you have real feedback or questions for the post, please contact us.
Not a trivial problem, definitely.
For me the biggest problem when doing search with HABTM relation was how to fetch the data from database not killing it. I am curious how fast is your approach...
Storing search parameters in session - not the best solution I think. User may be doing different searches in two browser windows and get unexpected results. It's also not too search-engine-friendly.