Paginating results from multiple tables using CakePHP
What you get from the CakePHP pagination helper is reason enough to use the CakePHP framework. However, what happens if you need to paginate results from multiple tables? I’ve set up a working demo to show an approach and have provided project files for download. In the app folder of the project files you’ll find a paginatedjoins.sql file to set up your tables. Remember to provide your own credentials in your config/database.php file.
This demo sets up a scheduler for a museum. A user can schedule various animal exhibits in various rooms. There are three tables of animal exhibits (barnyard animals, jungle animals, sea creatures), one table of animal types (animal_types), one table of exhibits rooms (rooms), one relationship table to relate animal types to animals (animal_relations), and one final table to relate animals to rooms (animals_rooms).
The paginated view of the exhibit schedule displays results from the animal_relations tables and the animals_rooms table. The key function that queries the paginated results is the index action in the schedule_managers controller.
function index($filter = null) {
//$joins1 = "INNER JOIN `animal_relations` AS `AnimalRelation` ON (`AnimalsRoom`.`animal_relation_id` = `AnimalRelation`.`id` AND `AnimalRelation`.`common_name` LIKE '" . $filter . "%')";
$joins1 = array(
'table' => 'animal_relations',
'alias' => 'Anim_Rel',
'type' => 'inner',
'foreignKey' => false,
'conditions'=> array(
'AnimalsRoom.animal_relation_id = Anim_Rel.id',
'Anim_Rel.common_name LIKE' => $filter . '%'
)
);
if ($this->data) { // user is attempting to search
if (!empty($this->data['ScheduleManager']['search_text']) && $this->data['ScheduleManager']['search_text'] != 'search by name') { // user is searching
//$joins1 = "INNER JOIN `animal_relations` AS `AnimalRelation` ON (`AnimalsRoom`.`animal_relation_id` = `AnimalRelation`.`id` AND `AnimalRelation`.`common_name` LIKE '" . $filter . "%')";
$joins1 = array(
'table' => 'animal_relations',
'alias' => 'Anim_Rel',
'type' => 'inner',
'foreignKey' => false,
'conditions'=> array(
'AnimalsRoom.animal_relation_id = Anim_Rel.id',
'Anim_Rel.common_name LIKE' => $this->data['ScheduleManager']['search_text'] . '%'
)
);
$this->set('filter_search', $this->data['ScheduleManager']['search_text']);
} else {
$this->Session->setFlash(null, 'flash_error', array('msg' => 'You must provide a search value.'));
}
}
$this->paginate = array(
'AnimalsRoom' => array(
'limit' => 10,
'order' => array('AnimalsRoom.start_date' => 'asc'),
'contain' => array(
'Room' => array(
'fields' => array('name')
),
'AnimalRelation' => array('common_name')
),
'fields' => array('id', 'animal_id', 'animal_type', 'animal_relation_id', 'room_id', 'start_date', 'end_date', 'priority'),
'joins' => array($joins1)
)
);
// get paginated results
$room_content = $this->paginate('AnimalsRoom');
/* $room_content is structured as follows
Array
(
[0] => Array
(
[AnimalsRoom] => Array
(
[id] =>
[animal_id] =>
[animal_type] =>
[animal_relation_id] =>
[room_id] =>
[start_date] =>
[end_date] =>
[priority] =>
)
[AnimalRelation] => Array
(
[common_name] =>
[id] =>
)
[Room] => Array
(
[name] =>
[id] =>
)
)
)
*/
$this->set('data', $room_content);
}
I’ve commented the join statements in plain SQL before the same queries prepared for Cake. So with the join as well as the belongsTo relationship the rooms table has with the animals_rooms table, we’re able to get the animal name, type, related room and start/end date in one query.
One important thing to notice in the view….you need to include the table name when setting up the sort keys. This snippet is from schedule_managers/index.ctp.
sort('common_name', 'AnimalRelation.common_name');?> sort('animal_type', 'AnimalsRoom.animal_type');?> sort('room', 'Room.name');?> sort('start_date', 'AnimalsRoom.start_date');?> sort('end_date', 'AnimalsRoom.end_date');?>
Using the table name tells the paginator helper how the results are structured.
Also, if you need to paginate across tables that live in a totally different database, please view the Queries across multiple databases with CakePHP tutorial.
Again, if you’d like to see it in action here’s a working demo and some project files for download. In the app folder of the project files you’ll find a paginatedjoins.sql file to set up your tables. Remember to provide your own credentials in your config/database.php file. I hope this can help some people out. Please let me know in the comments if you have any questions.
