Monday, March 9, 2009

How To Paginate Self-Referential HABTM Relationships in CakePHP 1.2

Monday, March 9, 2009
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
)
)
)
)
)

6 comments:

  1. Hi Kyo,

    Do you have a friends table on this? Or is the friend taken from the user table?

    Thanks

    ReplyDelete
  2. @Nu Order Webs
    No, there's no need to create a friend table. You only need 2 tables, users and users_friends. It's retrieving data as Friend from the users table.

    ReplyDelete
  3. Hi,

    Thanks a lot for the example.

    How would you structure it so that you would not just get the User's friends, but also the people to whom the User is a friend. With your data set, this is irrelevant, because all relationships are reciprocal.

    More importantly, how would you structure it so that you would get back only a list of people to whom the user is a friend?

    I have been playing around with the joins and haven't figured it out yet.

    Thanks a lot for your helpful tutorial.

    Josh

    ReplyDelete
  4. My question is similar to Josh's. How do you find your User's friend's content? i.e. How can I see all the posts that my friends have posted?

    Great tutorial!

    ReplyDelete
  5. thanks man for this ....
    god bless you

    ReplyDelete
  6. I know this is an old article but have the same question as the others. This association seems to go one way. Meaning, there's no readily apparent way to see who had you as a friend. Ideally, this relationship could be completely reciprocal; meaning if Tom is friends with Sue, Sue should be friend with Tom.

    ReplyDelete

Please feel free to post your comment about this article.

 
JamNite ◄Design by Pocket, BlogBulk Blogger Templates