Recently I got asked how you could paginate data from a HABTM (hasAndBelongsToMany) relationship. As the answer could be interesting for others, too, I will show a potential solution here.

Let’s say you have the two models Category and Product, and you want to paginate the Products of a certain Category. Your table definition looks like:

create table categories (
    id int(11) not null auto_increment,
    name varchar(255) not null,
    primary key (id)
);

create table products (
    id int(11) not null auto_increment,
    name varchar(255) not null,
    primary key (id)
);

create table categories_products (
    category_id int(11) not null,
    product_id int(11) not null
);

And the models are:

// app/models/category.php
class Category extends AppModel {
    var $hasAndBelongsToMany = array('Product' );
}

respectively

// app/models/product.php
class Product extends AppModel {
    var $hasManyAndBelongsToMany = array('Category');
}

Before we move on, let’s add some test data to the tables:

insert into categories (id, name) values (1, 'PHP');
insert into categories (id, name) values (2, 'Framework');

insert into products (id, name) values (1, 'CakePHP');
insert into products (id, name) values (2, 'Zend');
insert into products (id, name) values (3, 'Symfony');
insert into products (id, name) values (4, 'Rails');
insert into products (id, name) values (5, 'Django');

insert into categories_products (category_id, product_id) values (1, 1);
insert into categories_products (category_id, product_id) values (1, 2);
insert into categories_products (category_id, product_id) values (1, 3);

insert into categories_products (category_id, product_id) values (2, 1);
insert into categories_products (category_id, product_id) values (2, 2);
insert into categories_products (category_id, product_id) values (2, 3);
insert into categories_products (category_id, product_id) values (2, 4);
insert into categories_products (category_id, product_id) values (2, 5);

Now we want to paginate all products in the category “PHP”. For this purpose we write the following code:

// app/controllers/categories_controller.php
class CategoriesController extends AppController {
    var $paginate = array('limit' => 2);

    function index() {
        $data = $this->paginate('Category', array('Category.id' => 1));
        debug($data);
        exit;
    }
}

The output of this code is:

Array
(
    [0] => Array
        (
            [Category] => Array
                (
                    [id] => 1
                    [name] => PHP
                )
            [Product] => Array
                (
                    [0] => Array
                        (
                            [id] => 1
                            [name] => CakePHP
                        )
                    [1] => Array
                        (
                            [id] => 2
                            [name] => Zend
                        )
                    [2] => Array
                        (
                            [id] => 3
                            [name] => Symfony
                        )
                )
        )
)

Obviously the wrong approach as we get three products instead of two. Hm, maybe we have to use the Product model in the paginate statement. Let’s try it:

// app/controllers/categories_controller.php
class CategoriesController extends AppController {
    var $paginate = array('limit' => 2);

    function index() {
        $data = $this->paginate('Product', array('Category.id' => 1));
        debug($data);
        exit;
    }
}

This causes the following error:

SQL Error: 1054: Unknown column 'Category.id' in 'where clause'

Hm. Couldn’t we use somehow the mapping table required for our HABTM association? Sure, we can create a model for it:

// app/model/categories_product.php
class CategoriesProduct extends AppModel {
    var $belongsTo = array('Category', 'Product');
}

And use this model in the paginate statement:

// app/controllers/categories_controller.php
class CategoriesController extends AppController {
    var $paginate = array('limit' => 2);

    function index() {
        $data = $this->paginate('CategoriesProduct', array('Category.id' => 1));
        debug($data);
        exit;
    }
}

This time we get the expected result:

Array
(
    [0] => Array
        (
            [CategoriesProduct] => Array
                (
                    [category_id] => 1
                    [product_id] => 1
                )
            [Category] => Array
                (
                    [id] => 1
                    [name] => PHP
                )
            [Product] => Array
                (
                    [id] => 1
                    [name] => CakePHP
                )
        )
    [1] => Array
        (
            [CategoriesProduct] => Array
                (
                    [category_id] => 1
                    [product_id] => 2
                )
            [Category] => Array
                (
                    [id] => 1
                    [name] => PHP
                )
            [Product] => Array
                (
                    [id] => 2
                    [name] => Zend
                )
        )
)

If there is a better solution, please let me know ;-)