Basic search with CakePHP Part 1 – HABTM, joins and pagination
This will be a multi-part demonstration for building basic search functionality using CakePHP. Part 1 focuses on searching across models that have HasAndBelongsToMany relationships. Here’s a working demo of Part 1.
You see this type of search when looking for items that share similar properties. In the demo, it’s Bars that have Features (WiFi, TV, etc), Scenes (Lounge, Afterhours, etc) and Neighborhoods (Chelsea, Soho, etc). HABTM search is also useful for searching items that share Tags, Colors, Size, Flavor and on and on.
Part 1 sets up the models, designs the queries (based on an article by Nate on the Cake site), and presents a paginated view of the results. Part 2 will present the search criteria in a more user friendly way and add search refinements on the results page. Part 3 will add sorting.
Models – In the demo, we’re searching for Bars with various Features, Scenes and Neighborhoods….our models. The code for each is pretty straightforward….here’s the Bar model.
class Bar extends AppModel {
var $name = 'Bar';
var $actsAs = array('Containable');
var $hasAndBelongsToMany = array(
'Feature' => array(
'className' => 'Feature',
'foreignKey' => 'bar_id',
'associationForeignKey' => 'feature_id'
),
'Neighborhood' => array(
'className' => 'Neighborhood',
'foreignKey' => 'bar_id',
'associationForeignKey' => 'neighborhood_id'
),
'Scene' => array(
'className' => 'Scene',
'foreignKey' => 'bar_id',
'associationForeignKey' => 'scene_id'
)
);
}
We’re adding the Containable behavior as well as establishing the HATBM relationships with the other models. Here’s the Features model.
class Feature extends AppModel {
var $name = 'Feature';
var $actsAs = array('Containable');
var $hasAndBelongsToMany = array(
'Bar' =>
array(
'className' => 'Bar',
'foreignKey' => 'feature_id',
'associationForeignKey' => 'bar_id'
)
);
}
You can see the reference back to the Bars model. The Scenes and Neighborhoods models follow exactly the same pattern…so I won’t include them here. You also need to setup your MySQL tables. You can reference the HABTM section of the docs if you need to.
Controller – The controller handles the logic for all the pages in the search and begins with references to the Text and Javascript helpers. It also sets up the Paginator helper.
class BarsController extends AppController {
var $name = 'Bars';
var $helpers = array('Text', 'Javascript');
function beforeFilter() {
parent::beforeFilter();
$this->pageTitle = $this->name;
}
var $paginate = array(
'limit' => 10,
'order' => array(
'Bar.name' => 'asc'
)
);
Next comes the index action where users select their criteria.
function index () {
// clear the search criteria in the session
$this->Session->del($this->name);
$this->set('bars_total', $this->Bar->find('count'));
$this->set('features', $this->Bar->Feature->find('list'));
$this->set('scenes', $this->Bar->Scene->find('list'));
$this->set('neighborhoods', $this->Bar->Neighborhood->find('list'));
}
Notice we begin by deleting the Bars reference in the session. We’ll get to session later when talking about pagination. We’re also sending variables to the view for the Form helper to use when creating the multiple select boxes.
Next comes the search action. There’s a fair amount of redundancy here that could be abstracted for far less lines of code. That’s exactly what Nate does in his article. But for purposes of understanding what’s going on, I’ve hardcoded several steps.
function search () {
if (!empty($this->data)) {
if (isset($this->data['Feature']['Feature']) && !empty($this->data['Feature']['Feature'])) {
$params = array(
'conditions' => array('Feature.id' => $this->data['Feature']['Feature'])
);
$this->Session->write($this->name . '.Feature', $this->Bar->Feature->find('list', $params));
}
if (isset($this->data['Scene']['Scene']) && !empty($this->data['Scene']['Scene'])) {
$params = array(
'conditions' => array('Scene.id' => $this->data['Scene']['Scene'])
);
$this->Session->write($this->name . '.Scene', $this->Bar->Scene->find('list', $params));
}
if (isset($this->data['Neighborhood']['Neighborhood']) && !empty($this->data['Neighborhood']['Neighborhood'])) {
$params = array(
'conditions' => array('Neighborhood.id' => $this->data['Neighborhood']['Neighborhood'])
);
$this->Session->write($this->name . '.Neighborhood', $this->Bar->Neighborhood->find('list', $params));
}
} else {
$this->data['Feature']['Feature'] = array();
if ($this->Session->check($this->name . '.Feature')) {
$this->data['Feature']['Feature'] = array_flip($this->Session->read($this->name . '.Feature'));
}
$this->data['Scene']['Scene'] = array();
if ($this->Session->check($this->name . '.Scene')) {
$this->data['Scene']['Scene'] = array_flip($this->Session->read($this->name . '.Scene'));
}
$this->data['Neighborhood']['Neighborhood'] = array();
if ($this->Session->check($this->name . '.Neighborhood')) {
$this->data['Neighborhood']['Neighborhood'] = array_flip($this->Session->read($this->name . '.Neighborhood'));
}
}
// looking for DISTINCT Bar.ids to pass to pagination
$params = array(
'contain' => false,
'fields' => array('DISTINCT (Bar.id) as id'),
'joins' => array()
);
// FEATURE
if (isset($this->data['Feature']['Feature']) && !empty($this->data['Feature']['Feature'])) {
$joins1 = array(
'table' => 'bars_features',
'alias' => 'BarsFeature',
'type' => 'inner',
'foreignKey' => false,
'conditions'=> array('BarsFeature.bar_id = Bar.id')
);
$joins2 = array(
'table' => 'features',
'alias' => 'Feature',
'type' => 'inner',
'foreignKey' => false,
'conditions'=> array(
'Feature.id = BarsFeature.feature_id',
'Feature.id' => $this->data['Feature']['Feature']
)
);
array_push($params['joins'], $joins1);
array_push($params['joins'], $joins2);
}
// SCENE
if (isset($this->data['Scene']['Scene']) && !empty($this->data['Scene']['Scene'])) {
$joins1 = array(
'table' => 'bars_scenes',
'alias' => 'BarsScene',
'type' => 'inner',
'foreignKey' => false,
'conditions'=> array('BarsScene.bar_id = Bar.id')
);
$joins2 = array(
'table' => 'scenes',
'alias' => 'Scene',
'type' => 'inner',
'foreignKey' => false,
'conditions'=> array(
'Scene.id = BarsScene.scene_id',
'Scene.id' => $this->data['Scene']['Scene']
)
);
array_push($params['joins'], $joins1);
array_push($params['joins'], $joins2);
}
// NEIGHBORHOOD
if (isset($this->data['Neighborhood']['Neighborhood']) && !empty($this->data['Neighborhood']['Neighborhood'])) {
$joins1 = array(
'table' => 'bars_neighborhoods',
'alias' => 'BarsNeighborhood',
'type' => 'inner',
'foreignKey' => false,
'conditions'=> array('BarsNeighborhood.bar_id = Bar.id')
);
$joins2 = array(
'table' => 'neighborhoods',
'alias' => 'Neighborhood',
'type' => 'inner',
'foreignKey' => false,
'conditions'=> array(
'Neighborhood.id = BarsNeighborhood.neighborhood_id',
'Neighborhood.id' => $this->data['Neighborhood']['Neighborhood']
)
);
array_push($params['joins'], $joins1);
array_push($params['joins'], $joins2);
}
// query
$bars = array();
foreach ($this->Bar->find('all', $params) as $row) {
array_push($bars, $row['Bar']['id']);
}
// do basic pagination; id must appear in list of ids from previous query
$data = $this->paginate('Bar', array('Bar.id' => $bars));
$this->set('data', $data);
}
So what’s going on here? First, the search action is repeatedly called as users page through the results. The first time they arrive, data is sent from the form on the index page in $this->data. The first branch of the if statement (lines 3 – 24) sets the user’s search criteria in the session. As the user pages through the results, the action references the session rather than $this->data (lines 28 – 41).
The last section builds the search parameters based on the criteria. You can see we start by declaring a $params variable followed by three blocks of “joins”. The joins work to search across models. For example in the case of Features, if a Bar has at least one of the Features listed in the search criteria, the join will pull it into the result. This also means the Bar will be returned more than once if it has more than one Feature listed. That’s why we’ve declared that we want DISTINCT Bar ids in the $params.
You may have also noticed we’re only looking for an array of Bar ids from this query. I wanted to keep the Paginator’s task very straight forward. So once we have the list of Bar ids that meet the criteria, we tell the Paginator to make a basic query and to only include Bars whose id appears in the list from the big joins query.
Again, you can apply your own refactoring strategy to tackle the obvious redundancy in this action. Finally we pass the list of Bar data to the view as $data.
The last action simply handles the detail page and uses a passed Bar id to query the Bar model.
function detail ($id = null) {
$params = array(
'conditions' => array('Bar.id' => $id)
);
$this->data = $this->Bar->find('first', $params);
}
}
Views – In Part 1, the views take advantage of the various helpers to present the search criteria and results. Here’s the index which asks the user to select some criteria.
<?php echo $javascript->link('jquery-latest.min.js', false); ?>
Search for Bars
We have <?php echo $bars_total; ?> bars in our database. Use the form below to search based on your perferences. Selecting more preferences returns more results.
<table class='ui_search'>
<tr>
<?php
echo $form->create("Bar", array("action" => "search"));
echo $form->input("Feature", array("class" => "mult", "div" => false, "before" => "
", "after" => "clear | select all
"));
echo $form->input("Scene", array("class" => "mult", "div" => false, "before" => "
", "after" => "clear | select all
"));
echo $form->input("Neighborhood", array("class" => "mult", "div" => false, "before" => "
", "after" => "clear | select all
"));
echo "</tr><tr class='ui_search'><td colspan='3' class='end'>";
echo $form->end('Search');
echo "</td>";
?>
</tr>
</table>
The index uses the Form helper to create the multiple select fields. We also bind two jQuery functions to handle the ‘clear’ and ’select all’ links under the fields. You’ll need to download the latest minified version of jQuery, rename it ‘jquery-latest.min.js’ and place it in your ‘js’ folder in your webroot.
Here’s the search page.
<?php echo $javascript->link('jquery-latest.min.js', false); ?>
<style type="text/css">
.results {padding: 10px 20px; background-color: #ccc;}
h3 {padding-top: 0;}
.highlight {background-color: #fff;}
</style>
<script type="text/javascript">
$(document).ready(function(){
$("div.results:odd").css({
"background-color" : "#e6e6e6"
});
$("div.results:even").css({
"background-color" : "#ccc"
});
$("div.results a").css({
"background-color" : "transparent"
});
});
</script>
<?php $records_total = $paginator->counter(array('format' => '%count%')); ?>
<h2>We found <?php echo $records_total; ?> bars!</h2>
<?php
if (!$session->check("Bars.Feature") && !$session->check("Bars.Scene") && !$session->check("Bars.Neighborhood")) {
echo "<p>You provided no criteria...so we gave you everything. [ " . $html->link('search again', array('controller' => 'bars', 'action' => 'index')) . " ]</p><br />";
} else {
echo "<p>You searched for bars with the following criteria. [ " . $html->link('search again', array('controller' => 'bars', 'action' => 'index')) . " ]</p><br />";
}
$features = array();
if ($session->check("Bars.Feature")) {
$a = $session->read("Bars.Feature");
foreach ($a as $r) {
array_push($features, $r);
}
echo "<p>Features: " . $text->toList($features, 'or') . "</p>";
}
$scenes = array();
if ($session->check("Bars.Scene")) {
$a = $session->read("Bars.Scene");
foreach ($a as $r) {
array_push($scenes, $r);
}
echo "<p>Scenes: " . $text->toList($scenes, 'or') . "</p>";
}
$neighborhoods = array();
if ($session->check("Bars.Neighborhood")) {
$a = $session->read("Bars.Neighborhood");
foreach ($a as $r) {
array_push($neighborhoods, $r);
}
echo "<p>Neighborhoods: " . $text->toList($neighborhoods, 'or') . "</p>";
}
?>
<br /><hr><br />
<?php
// page numbers
$paginator->numbers();
if ($records_total > 0) {
echo "<div class='results'>";
// render the previous link
echo $paginator->prev('prev',null,null,null) . " | ";
echo $paginator->counter(array(
'format' => 'Page %page% of %pages%, showing %current% records out of %count% total'
));
// render the 'next' link
echo " | " . $paginator->next('next', null, null, null);
echo "</div>";
}
foreach ($data as $row) {
echo "<div class='results'>";
echo "<h3>" . $row['Bar']['name'] . "</h3><br />";
echo "<p>" . $row['Bar']['address1'] . "</p>";
echo "<p>" . $row['Bar']['state'] . ", " . $row['Bar']['city'] . " " . $row['Bar']['zip'] . "</p><br />";
$a = array();
foreach ($row['Feature'] as $r) {
array_push($a, $r['name']);
}
echo "<p>Features: " . $text->highlight($text->toList($a), $features) . "</p>";
$a = array();
foreach ($row['Scene'] as $r) {
array_push($a, $r['name']);
}
echo "<p>Scene: " . $text->highlight($text->toList($a), $scenes) . "</p>";
$a = array();
foreach ($row['Neighborhood'] as $r) {
array_push($a, $r['name']);
}
echo "<p>Neighborhood: " . $text->highlight($text->toList($a), $neighborhoods) . "</p><br />";
echo "<p>" . $text->truncate($row['Bar']['description'], 100, '...', false, true) . "</p>";
echo "<br />[ " . $html->link('detail', array('controller' => strtolower($this->params['controller']), 'action' => 'detail', $row['Bar']['id'])) . " ]";
echo "</div>";
}
if ($records_total > 0) {
echo "<div class='results'>";
// render the previous link
echo $paginator->prev('prev',null,null,null) . " | ";
echo $paginator->counter(array(
'format' => 'Page %page% of %pages%, showing %current% records out of %count% total'
));
// render the 'next' link
echo " | " . $paginator->next('next', null, null, null);
echo "</div>";
}
echo "<div id='content'><p>" . $html->link('search again', array('controller' => 'bars', 'action' => 'index')) . "</p></div>";
?>
So what’s going on here? At the top we’re using some inline styles and jQuery to stripe the result rows. We’re also displaying the user’s session criteria that was stored in the session variable by the controller (lines 35 – 66). The view uses the Text helper’s toList() method to nicely render the lists of criteria. We’re also storing those lists to use later.
The next block uses the Paginator to render the previous and next links as well as the page count (lines 75 – 91).
The data loop renders the information for each Bar returned by the query. Again we’re using the Text helper’s toList() method but wrapping it with the highlight() method to highlight the properties that matched the search criteria. We also truncate() the description and provide a link to the details view.
<?php
echo "<div id='header'><h3>" . $this->data[$this->params['models'][0]]['name'] . "</h3></div>";
echo "<div id='content'><p>" . $this->data[$this->params['models'][0]]['address1'] . "</p>";
echo "<p>" . $this->data[$this->params['models'][0]]['state'] . ", " . $this->data[$this->params['models'][0]]['city'] . " " . $this->data[$this->params['models'][0]]['zip'] . "</p>";
echo $html->link('map', $this->data[$this->params['models'][0]]['url'], array('target' => 'top')) . "</p></div>";
echo "<div id='content'>";
$a = array();
foreach ($this->data['Feature'] as $r) {
array_push($a, $r['name']);
}
echo "<p>Features: " . $text->toList($a) . "</p>";
$a = array();
foreach ($this->data['Scene'] as $r) {
array_push($a, $r['name']);
}
echo "<p>Scene: " . $text->toList($a) . "</p>";
$a = array();
foreach ($this->data['Neighborhood'] as $r) {
array_push($a, $r['name']);
}
echo "<p>Neighborhood: " . $text->toList($a) . "</p>";
echo "</div>";
echo "<div id='content'><p>" . $this->data[$this->params['models'][0]]['description'] . "</p></div>";
echo "<div id='content'><p>" . $html->link('back to results', array('controller' => 'bars', 'action' => 'search')) . "</p></div>";
?>
The details view simply displays data from the passed Bar id.
Please check out the demo to see it all in action. And come back for Part 2 where we’ll work on the presentation of the search criteria as well as provide search refinement functionality.
June 19th, 2009 at 1:53 pm
what version of cake is this for? would youmake this available for download? I copied everything as you have it…removed neighborhood as i only need 2 HABTM but it will not work.I get error in the joins
June 19th, 2009 at 7:46 pm
This was built using version 1.2.3.8166. I can post the source files in a little while. But what exactly is your error message? Also, do you have the HABTM tables set up (ie. bars_features, bars_scenes)?
July 7th, 2009 at 2:36 am
[...] This is part 2 of the Basic search with CakePHP where we’ll present the search criteria in a more user-friendly way. The files here build on part 1. [...]
August 27th, 2009 at 6:30 pm
Hi
Thanks for the post, can you provide the project files? Makes it easier to see how things work together.