Pagination of data from a HABTM relationship

Published on and tagged with cakephp  pagination

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 {
    public $hasAndBelongsToMany = array('Product');
}

respectively

// app/models/product.php
class Product extends AppModel {
    public $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 {
    public $paginate = array('limit' => 2);
		
    public 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 {
    public $paginate = array('limit' => 2);
		
    public 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 {
    public $belongsTo = array('Category', 'Product');
}

And use this model in the paginate statement:

// app/controllers/categories_controller.php
class CategoriesController extends AppController {
    public $paginate = array('limit' => 2);
		
    public function index() {	
        $data = $this->paginate('CategoriesProduct', array('Category.id' => 1));
        debug($data);
        exit;		
    }
}

This time we get the expected result (at least up to RC4 of CakePHP 1.2):

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

However, in CakePHP 1.2.1.8004 the previous solution no longer works. The CategoriesProduct model is no longer needed. And by following the tutorial about ad-hoc joins we have to change the CategoriesController to:

// app/controllers/categories_controller.php
class CategoriesController extends AppController {
    public $paginate = array('Product' => array('limit' => 2, 'joins' => array( 
        array( 
            'table' => 'categories_products', 
            'alias' => 'CategoriesProduct', 
            'type' => 'inner',  
            'conditions'=> array('CategoriesProduct.product_id = Product.id') 
        ), 
        array( 
            'table' => 'categories', 
            'alias' => 'Category', 
            'type' => 'inner',  
            'conditions'=> array( 
                'Category.id = CategoriesProduct.category_id', 
                'Category.id' => 1 
            ) 
        ))));

    public function index() {
        $data = $this->paginate('Product');
        debug($data);
        exit;
    }	
}

With those changes the debug output should now look like:

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

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

Update 2009-02-01: Adding solution for CakePHP 1.2.1.8004.

101 comments baked

Bake a comment




(for code please use <code>...</code> [no escaping necessary])

© daniel hofstetter. Licensed under a Creative Commons License