Skip to content
Share this..

Using CakePHP Pagination with HABTM tables.

2008 May 21
tags: 1.1, CakePHP, HABTM, pagination
by Eddie

This works with CakePHP Version 1.1 Only ! Version 1.2 has paginate component built in.

So I love using the Pagination Helper and Pagination Components because they are so sweet and so easy. The trouble I hit was in HABTM relationships.

An example is product categories. A product like Apple may belong to many categories like Fruits and Food.

The category Food may have several products, such as Apples, Pizzas and Burgers.

So if our users view a Category page like ‘Fruits’ then we want to be able to restrict the items shown to the Fruit category, but keep our pagination.
It can be done with little effort.

Getting Started

To get the pagination components for Cakephp 1.1 , and learn how to use it in basic models, read this article by Andy Dawson.

Once you understand it’s use with a simple model, we can apply it to a Has And Belongs To Many model.

Advancing to HABTM

For our example we will edit only 2 files, 3 if you don’t have the HABTM model yet.

For the action ‘View’ we will want to show all products that belong to this category in addition to details on the category itself.

So when users land on the details page for ‘Fruits’ , the associated products would be items that matched, like ‘apples’, ‘oranges’, and ‘bananas’ but all other products like burgers should not show up.

controllers/categories_controller.php

function view($id = null) {
	if (!$id) {
		$this->Session->setFlash('Invalid id for Category.');
		$this->redirect('/categories/index');
	}
	$cat= $this->Category->read(null, $id);
	$this->set('category',$cat);
	$this->set('parent', $this->Category->findById($cat['Category']['parent_id']));
	$this->set('children', $this->Category->findAll('`Category`.`parent_id`='.$id));
 
	//allow products ot be sorted
	$this->Product->recursive=2;
	$criteria=null;
	list($order,$limit,$page) = $this->Pagination->init($criteria); // Added
 
	//NOTE: we use the relational table for the criteria and query
	$criteria='`ProductsCategory`.`category_id`='.$id;
	$data = $this->ProductsCategory->findAll($criteria, null, $order, $limit, $page); 
	$this->set('products', $data);
}

This will set up two variables to be used by our view, $category and $products.

views/categories/view.thtml

<h3>Products in this Category</h3>
<div id="pagination">
	<?php
	if (!empty($products)):
		$pagination->setPaging($paging); // Initialize the pagination variables
		/*
		*Create form to sort results
		*/
		echo $ajax->form(NULL,NULL,array("update" => $pagination->_pageDetails['ajaxDivUpdate'],"id"=>'paginationForm'));
		echo $pagination->resultsPerPageSelect()." ";
		$sorts = Array (
			"id::ASC::Product",
			"id::DESC::Product",
			"name::ASC::Product",
			"name::DESC::Product",
		);
		echo $pagination->sortBySelect($sorts);
		echo $ajax->submit("Submit",array("update" => $pagination->_pageDetails['ajaxDivUpdate'],"id"=>'paginationSubmit'));
		echo $ajax->observeForm('paginationForm',array("frequency"=>1,"update" => $pagination->_pageDetails['ajaxDivUpdate']));
		echo "<script type=\"text/javascript\">document.getElementById('paginationSubmit').hide();</script>";
		foreach ($products as $output)
		{
			//create td for values to add to array
			$values=" ";
			if($output['Product']['isorganic']==1) $values.= '<a href="/info/organic" title="Learn about this icon"><img src="/img/value_icons/organic.png" alt="Organic" /></a>';
			if($output['Product']['isnatural']==1) $values.=  '<a href="/info/natural" title="Learn about this icon"><img src="/img/value_icons/natural.png" alt="Natural" /></a>';
			if($output['Product']['isrecycled']==1) $values.=  '<a href="/info/recycled" title="Learn about this icon"><img src="/img/value_icons/recycled.png" alt="Recycled" /></a>';
			if($output['Product']['isdonation']==1) $values.=  '<a href="/info/donates" title="Learn about this icon"><img src="/img/value_icons/donates.png" alt="Donations Made" /></a>';
			$actions=' ';
			if($rights>=2) $actions.='   '.$html->link('Edit','/products/edit/' . $output['Product']['id']);
			if($rights==4) $actions.='   '.$html->link('Delete','/products/delete/' . $output['Product']['id'], null, 'Are you sure you want to delete id ' . $output['Product']['name']);
			$id=$output['Product']['id'];
			$title=$html->link($output['Product']['name'], $goto.$output['Product']['id']);
			$company=$output['Product']['Company']['name'];
			$description=$output['Product']['description'];
			$image=$html->image('uploads/'.$output['Product']['imageurl'],array('width'=>'120','alt'=>'Product Image'));
			echo '<div class="listviewrecord">';
				echo '<table class="listviewtable">';
					echo '<tr><td colspan="2"><h2>'.$title.'</h2></td></tr>';
					echo '<tr><td width="200">'.$image.'</td>'.
					'<td rowspan="2" valign="top"><h3> Produced by: '.$company.
					'</h3>'.$description.'<h6> Added on '.
					$date->regularize($output['Product']['dateAdded']).
					'</td></tr>';
					echo '<tr><td>'.$values.'</td></tr>';
					echo '<tr><td colspan="2">'.$actions.'</td></tr>';
				echo '</table>';
			echo '</div>';
		}//end for each record
		echo $this->renderElement('pagination');
	else:
		// no products
		echo '<h4>No Products have been assigned to this Category</h4>';
		echo 'You may assign Categories while viewing a '.$html->link('Product','/products').'.';
	endif;
	?>
	</div>
</div>

Now in oder for the query you set up in the controller to work you need to have the proper associations in the product model, the category model, and the products_category model.

models/Products_category.php

<?php
class Category extends AppModel{
	var $name = 'Category';
	var $displayField = 'name';
	var $validate = array(
				'name' => VALID_NOT_EMPTY,
				'description' => VALID_NOT_EMPTY,
			);
	var $recursive = -1;
 
	var $hasMany = array (
			'Alternatives' => array(
					'className' => 'Alternative',
					'conditions'=>'',
					'order'=>'Alternatives.name',
					'foreignkey'=>'category_id'
					)
			);
	var $hasAndBelongsToMany = array(
			'Product' => array(
					'className'=> 'Product',
					'joinTable'      => 'products_categories',
					'foreignKey'      => 'category_id',
					'associationForeignKey'      => 'product_id',
					'conditions'      => '',
					'order'      => 'Product.name',
					'limit'      => '',
					'unique'      => true,
					'finderQuery'      => '',
					'deleteQuery'      => ''
				)
			);
}
?>

from → CakePHP

2 Responses leave one →
  1. Jean Luis permalink
    November 11, 2011

    Paginate a Find Result

    Hello, im looking for a solution to this little problem of mine, i need to paginate tha data resulting from a custom find, bellow you can see the code that im using, at this point i get all the data that i need, but i”d like to paginate it because sometimes it returns a large amount of data.

    Anyone have a suggestion??

    THX in advance

    PS: if you anything else code wise or me being more specific let me know

    //THIS FORCES TO DO THIS “SELECT * FROM table1 LEFT JOIN table2 WHERE…..”

    $this->Equipo->unbindModel(array(‘hasMany’=>array(‘Storages’)));
    $this->Equipo->bindModel(array(‘hasOne’=>array(‘Storages’=>array(
    ‘foreignKey’=>false,
    ‘conditions’=>array(‘Equipo.id_almacen = storages.id’)
    )
    )
    )
    );
    //IN THIS PART $ DATA GETS THE VALUE OF “SELECT * FROM table1 LEFT JOIN table2 WHERE…..”
    $data=$this->Equipo->find(‘all’,array(‘conditions’=>array(
    ‘part_number LIKE’=>”%”.$this->data['Equipo']['part_number'].”%”,
    ‘id_almacen LIKE’=>”%”.$this->data['Equipo']['id_almacen'].”%”,
    ‘equipo_central LIKE’=>”%”.$this->data['Equipo']['equipo_central'].”%”,
    ‘descripcion LIKE’=>”%”.$this->data['Equipo']['descripcion'].”%”
    ),
    )
    );

Trackbacks and Pingbacks

  1. Recent Links Tagged With "setflash" - JabberTags

Leave a Reply

Note: You can use basic XHTML in your comments. Your email address will never be published.

Subscribe to this comment feed via RSS