Paginate results filtered by condition on associated model (HABTM) using Containable

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.

score 6 · Accepted Answer · edited May 23 '17 at 11:52

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.

score 1 · Answer 2 · edited May 23 '17 at 11:59

1

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

edited May 23 '17 at 11:59

Community

1
1

answered Jul 10 '11 at 05:20

Dave

28,833
23
113
183

I tried to move `Category` condition to main `conditions` clause, but it just fails (because it still does not `JOIN` tables but rather does 2 `SELECT`s). – lxa Jul 10 '11 at 14:30
@Ixa - I don't believe I suggested to do that - unless you've converted the whole thing to join. – Dave Jul 10 '11 at 16:24
you didn't, I just logically extended your comment _When you put the condition in the nested Contain, you're asking it to retrieve only the Categories with that ID_, - so that moving that condition from nested `Contain` to main `conditions` would impose it to whole query (as it actually did, but it still didn't include the `JOIN`) – lxa Jul 10 '11 at 18:11
My comment was meant to point out the error in logic of the current code, but it wasn't meant as an answer - My answer was where I specified that I could not get what you were doing to work using Contain - instead, try Joins instead. Believe me, I'd rather use Contain too - I tried 642 different ways to try to get it to work. In the end, though, I ended up getting it to work fairly easily with Joins. – Dave Jul 10 '11 at 20:15
actually I made it work finally using `Containable` - see my own answer – lxa Jul 10 '11 at 20:39
Gotcha - guess that's where we differed - I needed to run conditions on 5-6+ HABTM tables. Glad you got it to work the way you were hoping! :) – Dave Jul 10 '11 at 20:58
`Containable` approach **will** work for many HABTM tables (you just have to add fake `hasOne` binding for each HABTM intermediate table), although only on 1st level on recursion; I'm not sure its possible to make it work for deeper associations (but I didn't check). In any case, `join`s approach is more intuitive (as it corresponds to normal SQL) and allows filtering on fields other than primary key - so it is universal, and probably should be preferred over `Containable`. – lxa Jul 10 '11 at 21:50

Paginate results filtered by condition on associated model (HABTM) using Containable

2 Answers2

Linked

Related