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