How To Paginate Self-Referential HABTM Relationships in CakePHP 1.2

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.phpclass 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 ) ) ) ))