Since I recently spent a fair amount of time searching for the solution to the problem of filtering by associated HABTM fields in CakePHP (with pagination, of course) it seemed only right to share my solution. Much of this information was gleamed from http://forge.mysql.com/wiki/TagSchema.
As is common with most of these solutions, the first step is to create an association directly to the join table:
// In posts_controller.php $this->Post->bindModel(array( 'HasOne' => array( 'PostsTag' ) ), false);
Then, to select Posts which belong to any Tag in an array of tag_ids,
// In posts_controller.php $options = array( 'group' => 'Post.id', 'conditions' => array( 'PostsTag.tag_id' => $tags ) ); $posts = $this->Post->find('all', $options); //OR to paginate: $this->paginate = $options; $posts = $this->paginate();
The above example allows $tags to be either a string ($tags =’1′) or an array of tags ($tags = array(‘1’, ‘2’, ‘3’)). CakePHP will handle creating the appropriate IN() conditions if $tags is an array.
Selecting Posts which contain ALL of the tags in an array of tag_ids requires only a small modification to the options array,
$options = array( 'groups' => 'Post.id HAVING COUNT(DISTINCT PostsTag.id) = ' . count($tags), 'conditions' => array( 'PostsTag.tag_id' => $tags ) );
Hopefully this saves someone hours of searching!