Cakebaker (Daniel Hofstetter) once wrote a great article entitled, "
Pagination of data from a HABTM relationship". The article discuss how to do CakePHP Pagination with a HABTM relationship. Inspired by his article I want to show how to paginate self-referential HABTM relationships. This implementation has been tested with CakePHP 1.2.1.8004 and won’t work with older versions.
Let’s say you have a User model, and you want to paginate the Friends of a certain User. Your table definition looks like:
create table users (
id int(11) not null auto_increment,
username varchar(32) not null,
primary key (id)
);
create table users_friends (
id int(11) not null auto_increment,
user_id int(11) not null,
friend_id int(11) not null,
primary key (id)
);
We can then add some test data to the tables:
insert into users (id, username) values (1, 'kevin');
insert into users (id, username) values (2, 'stephanie');
insert into users (id, username) values (3, 'michael');
insert into users (id, username) values (4, 'jennifer');
insert into users_friends (user_id, friend_id) values (1, 2);
insert into users_friends (user_id, friend_id) values (1, 3);
insert into users_friends (user_id, friend_id) values (2, 1);
insert into users_friends (user_id, friend_id) values (2, 4);
insert into users_friends (user_id, friend_id) values (3, 1);
insert into users_friends (user_id, friend_id) values (3, 4);
insert into users_friends (user_id, friend_id) values (4, 2);
insert into users_friends (user_id, friend_id) values (4, 3);
And make a self-referential join in your User model:
class User extends AppModel {
var $name = 'User';
var $hasAndBelongsToMany = array(
'Friend' => array(
'className' => 'User',
'joinTable' => 'users_friends',
'foreignKey' => 'user_id',
'associationForeignKey' => 'friend_id',
'unique' => true,
)
);
}
Now we want to paginate all friends in the user “kevin”. By following the tutorial "
Quick Tip - Doing Ad-hoc Joins in Model::find()", we’ll create a controller:
// app/controllers/friends_controller.php
class FriendsController extends AppController {
function index() {
$this->paginate = array(
'Friend' => array(
'limit' => 2,
'joins' => array(
array(
'table' => 'users_friends',
'alias' => 'UsersFriend',
'type' => 'inner',
'conditions'=> array(
'UsersFriend.friend_id = Friend.id',
),
),
array(
'table' => 'users',
'alias' => 'User',
'type' => 'inner',
'conditions'=> array(
'User.id = UsersFriend.user_id',
'User.id' => 1
)
)
)
)
);
$data = $this->paginate('Friend');
debug($data);
exit;
}
}
The debug output should now look like:
Array
(
[0] => Array
(
[Friend] => Array
(
[id] => 2
[username] => stephanie
[0] => Array
(
[id] => 1
[username] => kevin
[UsersFriend] => Array
(
[id] => 3
[user_id] => 2
[friend_id] => 1
)
)
[1] => Array
(
[id] => 4
[username] => jennifer
[UsersFriend] => Array
(
[id] => 4
[user_id] => 2
[friend_id] => 4
)
)
)
)
[1] => Array
(
[Friend] => Array
(
[id] => 3
[username] => michael
[0] => Array
(
[id] => 1
[username] => kevin
[UsersFriend] => Array
(
[id] => 5
[user_id] => 3
[friend_id] => 1
)
)
[1] => Array
(
[id] => 4
[username] => jennifer
[UsersFriend] => Array
(
[id] => 6
[user_id] => 3
[friend_id] => 4
)
)
)
)
)
