Foldi » Blog Archive » Basic search with CakePHP Part 2

Basic search with CakePHP Part 2 – search criteria

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.

Here’s the working example.

When presenting search criteria, it’s helpful to indicate what criteria will return the most results. In the example, you can see the criteria is listed in descending order according to their number of associated bars. For example, 371 bars in the database have a fireplace. Searching only using “Fireplace” as the criteria will return exactly 371 bars…just what we want.

To get the counts of each criteria and sort them correctly we’ll employ a sub-query as well as a grouping that keeps our find() calls to one per model. The only files we’ll touch here from part 1 are the controller and search results view.

First, just replace the “index” action in app/controllers/bars_controller.php with the following.

function index () {

		// clear the search criteria in the session
		$this->Session->del($this->name);

		$this->set('bars_total', $this->Bar->find('count'));

		// added for Part 2

		// FEATURE
		$joins1 = array(
			'table' => 'features',
			'alias' => 'Feature',
			'type' => 'inner',
			'foreignKey' => false,
			'conditions'=> array(
				'Feature.id = BarsFeature.feature_id'
			)
		);	

		// get all distinct features in bars_feature; order by count; join Feature for Feature name
		$params = array(
			'fields' => array('BarsFeature.feature_id', 'COUNT(BarsFeature.feature_id)', 'Feature.name'),
			'group' => 'BarsFeature.feature_id',
			'order' => array('COUNT(BarsFeature.feature_id) DESC'),
			'joins' => array($joins1)
		);
		$data = $this->Bar->BarsFeature->find('all', $params);	

		// set for view
		$values = array();
		foreach ($data as $row) {
			$values[$row['BarsFeature']['feature_id']] = $row['Feature']['name'] . " (" . $row[0]['COUNT(`BarsFeature`.`feature_id`)'] . ")";
		}
		$values_u = array_unique($values);
		$this->set('features', $values_u);

		// SCENE
		$joins1 = array(
			'table' => 'scenes',
			'alias' => 'Scene',
			'type' => 'inner',
			'foreignKey' => false,
			'conditions'=> array(
				'Scene.id = BarsScene.scene_id'
			)
		);	

		// get all distinct scenes in bars_scene; order by count; join Scene for Scene name
		$params = array(
			'fields' => array('BarsScene.scene_id', 'COUNT(BarsScene.scene_id)', 'Scene.name'),
			'group' => 'BarsScene.scene_id',
			'order' => array('COUNT(BarsScene.scene_id) DESC'),
			'joins' => array($joins1)
		);
		$data = $this->Bar->BarsScene->find('all', $params);	

		// set for view
		$values = array();
		foreach ($data as $row) {
			$values[$row['BarsScene']['scene_id']] = $row['Scene']['name'] . " (" . $row[0]['COUNT(`BarsScene`.`scene_id`)'] . ")";
		}
		$values_u = array_unique($values);
		$this->set('scenes', $values_u);

		// NEIGHBORHOOD
		$joins1 = array(
			'table' => 'neighborhoods',
			'alias' => 'Neighborhood',
			'type' => 'inner',
			'foreignKey' => false,
			'conditions'=> array(
				'Neighborhood.id = BarsNeighborhood.neighborhood_id'
			)
		);	

		// get all distinct scenes in bars_neighborhood; order by count; join Neighborhood for Neighborhood name
		$params = array(
			'fields' => array('BarsNeighborhood.neighborhood_id', 'COUNT(BarsNeighborhood.neighborhood_id)', 'Neighborhood.name'),
			'group' => 'BarsNeighborhood.neighborhood_id',
			'order' => array('COUNT(BarsNeighborhood.neighborhood_id) DESC'),
			'joins' => array($joins1)
		);
		$data = $this->Bar->BarsNeighborhood->find('all', $params);	

		// set for view
		$values = array();
		foreach ($data as $row) {
			$values[$row['BarsNeighborhood']['neighborhood_id']] = $row['Neighborhood']['name'] . " (" . $row[0]['COUNT(`BarsNeighborhood`.`neighborhood_id`)'] . ")";
		}
		$values_u = array_unique($values);
		$this->set('neighborhoods', $values_u);

		//

	}

So what’s going on here? We are selecting the id, count of that id and the associated name using a sub-query for the count and a join for the name. NOTE: This query requires MySQL 5. We then format the resulting array so Cake’s form helper can easily render the multiple selects. There’s obviously some redundancy here. But for the sake of the demo I didn’t want to abstract anything. You will likely want to roll these queries into a component you can reuse.

Next, we want to copy the queries into the “search” action in the same controller. Do this after the pagination query.

Next we want to alter the view for the results so we can allow users to refine their search. To set up easy CSS columns, I’ve used 960.gs. Just download the css file, place it in /app/webroot/css/ and reference it in your default layout like this.

echo $html->css('960');

Here’s the search results view file in /app/views/bars/

<?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%')); ?>

<?php //debug($session->read()); ?>

<div class='container_16'>
<div class='grid_6 alpha'> </div>

<div class='grid_10'>

<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 />

</div>
</div>

<div class='clear'></div>

<div class='container_16'>
<div class='grid_6 alpha'>
<p>Refine your search:</p><br />

<?php
	echo $form->create("Bar", array("action" => "search"));
	echo $form->input("Feature", array('multiple' => 'checkbox'));
	echo $form->input("Scene", array('multiple' => 'checkbox'));
	echo $form->input("Neighborhood", array('multiple' => 'checkbox'));

	echo $form->end('Search');
?>

</div>

<div class='grid_10'>

<?php

// page numbers
$paginator->numbers();

// set url arguements
//$paginator->options(array('url' => $this->passedArgs));

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>";

?>

</div>
</div>

Everything is the same here from part 1 except the addition of the columns and the form to refine your search. Notice also we’ve used checkboxes instead of multi-select boxes to present the criteria. But even more noticeable is how Cake automatically selects the criteria we used in our initial search. Amazing!

You will likely want to use some show/hide functionality to let the user expand and collapse the long list of refining criteria.

If you haven’t had a chance, check out the working example to see it in action.

This entry was posted on Sunday, June 28th, 2009 at 9:46 pm and is filed under CakePHP. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.