HABTM cakephp and pagination with fields in the join table

Paginating HABTM: Brief version.

What I needed to do was to figure out how to paginate based on data within a join table. Most join tables contain two foreign keys referencing the two parent tables. I had a third field called type_id that I used to state what kind of role each user played.

We have three tables

CREATE TABLE `users` (
`id` int(11) NOT NULL default '0',
`name` varchar(50) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;

CREATE TABLE `groups` (
`id` int(11) NOT NULL default '0',
`name` varchar(50) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;

Which has a has and belong join table called:

CREATE TABLE `users_groups` (
`user_id` int(11) default NULL,
`group_id` int(11) default NULL,
`type_id` int(10) unsigned NOT NULL,
FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
FOREIGN KEY (`group_id`) REFERENCES `groups` (`id`)
) ENGINE=InnoDB;

At this point we have the typical model files for groups and users. Which is enough to do queries between the two tables. Cakephp will automatically query the join table when doing pagination.

A problem arises now if we want to query the type_id as one of our conditions.

eg.
we have Bob
User - Bob who's in Group 'Car Group' and is an 'advisor type'. ie his type_id is 3.
User - Bob is also in Group 'Truck Group' and is a 'guest type' and type_id = 4

Some solutions have been:
1) change the users_groups join table and add an 'id'. This allows you to treat it as a model and paginate it. You also create a file in ./models/users_groups.php.
2) Instead of adding an 'id' and preserving the 'join' tableness of users_groups, you write a custom paginate() and paginateCount() query.

eg.
function paginate ($condition, ...) { $sql = 'Select....'; return $this->query($sql)}

My Solution:
create a file ./models/users_groups.php
class UsersGroup extends AppModel {

var $name = 'UsersGroup';

var $belongsTo = array('User', 'Group');
public $actsAs = array('Containable');
}

In your controller

function index() {
$this->paginate = array('UsersGroup' => array( 'contain' => 'Group', 'fields' => array('Group.*')) );

pr($this->paginate($this->User->UsersGroup, array('UsersGroup.user_id' => 1, 'UsersGroup.type_id' => 3)));
}

Conclusion:

That's it. Now you be able to find what groups bob is an advisor in.