Paginate results filtered by condition on associat

Question

I need to paginate list of Products belonging to specific Category (HABTM association).

In my Product model I have

var $actsAs = array('Containable');
var $hasAndBelongsToMany = array(
    'Category' => array(
        'joinTable' => 'products_categories'
    )
);

And in ProductsController

$this->paginate = array(
    'limit' => 20,
    'order' => array('Product.name' => 'ASC'),
    'contain' => array(
        'Category' => array(
            'conditions' => array(
                'Category.id' => 3
            )
        )
    )
);
$this->set('products', $this->paginate());

However, resulting SQL looks like this:

SELECT COUNT(*) AS `count` 
FROM `products` AS `Product` 
WHERE 1 = 1;

SELECT `Product`.`*` 
FROM `products` AS `Product` 
WHERE 1 = 1 
ORDER BY `Product`.`name` ASC 
LIMIT 20;

SELECT `Category`.`*`, `ProductsCategory`.`category_id`, `ProductsCategory`.`product_id` 
FROM `categories` AS `Category` 
JOIN `products_categories` AS `ProductsCategory` ON (`ProductsCategory`.`product_id` IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20) AND `ProductsCategory`.`category_id` = `Category`.`id`)
WHERE `Category`.`id` = 3

(I.e. it selects 20 Products and then queries their Categories)

while I'd need

SELECT COUNT(*) AS `count` 
FROM `products` AS `Product` 
JOIN `products_categories` AS `ProductsCategory` ON `ProductsCategory`.`product_id` = `Product`.`id`
JOIN `categories` AS `Category` ON `Category`.`id` = `ProductsCategory`.`category_id`
WHERE `Category`.`id` = 3;

SELECT `Product`.*, `Category`.*
FROM `products` AS `Product` 
JOIN `products_categories` AS `ProductsCategory` ON `ProductsCategory`.`product_id` = `Product`.`id`
JOIN `categories` AS `Category` ON `Category`.`id` = `ProductsCategory`.`category_id`
WHERE `Category`.`id` = 3
ORDER BY `Product`.`name` ASC 
LIMIT 20;

(I.e. select top 20 Products which belong to Category with id = 3)

Note: Possible solution without Containable would be (as Dave suggested) using joins. This post offers a very handy helper to build $this->paginate['joins'] to paginate over HABTM association.

Note: Still looking for more elegant solution using Containable than fake hasOne binding.

Answer 1

Finally I found a way to do what I want, so posting it as an answer:

To force JOIN (and be able to filter via condition on associated model) in Containable - you've got to use fake hasOne association.

In my case, code in ProductsController should be:

$this->Product->bindModel(array('hasOne' => array('ProductsCategory')), false);

$this->paginate = array(
    'limit' => 20,
    'order' => array('Product.name' => 'ASC'),
    'conditions' => array(
        'ProductsCategory.category_id' => $category
    ),
    'contain' => 'ProductsCategory'
);

$this->set('products', $this->paginate());

Note false as a second argument to bindModel - which makes binding persistent. This is needed because paginate() issues find('count') before find('all'), which would reset temporary binding. So you might want to manually unbindModel afterwards.

Also, if your condition includes multiple IDs in HABTM associated model, you might want to add 'group' => 'Product.id' into your $this->paginate[] (as Aziz has shown in his answer) to eliminate duplicate entries (will work on MySQL only).

UPDATE: However, this approach has one serious drawback compared to joins approach (suggested by Dave): condition can apply only to intermediate model's foreign key (category_id in my case); if you want to use condition on any other field in associated model - you'd probably have to add another bindModel('hasOne'), binding intermediate model to HABTM associated model.

Answer 2

When you put the condition in the nested Contain, you're asking it to retrieve only the Categories with that ID. So - it's doing what you're asking, but that's not what you want.

Though it seems like it should be possible, the only luck I've had doing what you're trying to do (after MANY hours and a few stackoverflow questions) is via Joins instead of Contain.

http://book.cakephp.org/view/1047/Joining-tables

It's not the exact same problem, but you can go through some of my code where I query against HABTM conditions (I answered my question at the bottom) here: Select All Events with Event->Schedule->Date between start and end dates in CakePHP