使用 Containable 按关联模型 (HABTM) 上的条件过滤的分页结果 [英] Paginate results filtered by condition on associated model (HABTM) using Containable

问题描述

我需要对属于特定Category(HABTM 关联)的Product 列表进行分页.

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

在我的 Product 模型中，我有

In my Product model I have

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

在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());

然而，生成的 SQL 看起来像这样:

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

(即它选择 20 个 Products 然后查询它们的 Categories)

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

虽然我需要

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;

(即选择属于 Category 且 id = 3 的前 20 个 Products)

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

注意:没有 Containable 的可能解决方案是(如 Dave 建议的)使用连接.这篇帖子提供了一个非常方便的帮助程序来构建 $this->paginate['joins'] 以对 HABTM 关联进行分页.

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.

注意:仍在寻找使用 Containable 比假的 hasOne 绑定更优雅的解决方案.

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

推荐答案

我终于找到了做我想做的事情的方法，因此将其发布为答案:

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

在 Containable 中强制 JOIN(并能够通过关联模型的条件过滤) - 你必须使用假的hasOne关联.

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

就我而言，ProductsController 中的代码应该是:

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

注意 false 作为 bindModel 的第二个参数 - 这使得绑定持久化.这是必需的，因为 paginate() 在 find('all') 之前发出 find('count') ，这将重置临时绑定.所以你可能想在之后手动unbindModel.

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.

此外，如果您的条件在 HABTM 关联模型中包含多个 ID，您可能需要添加 'group' =>'Product.id' 到您的 $this->paginate[](如 Aziz 在他的回答中所示)以消除重复条目(将适用于仅限 MySQL).

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

更新:然而，与连接方法相比，这种方法有一个严重的缺点(由 Dave 建议):条件只能应用于中间模型的外键(在我的情况下为 category_id)；如果您想在关联模型中的任何其他字段上使用条件 - 您可能需要添加另一个 bindModel('hasOne')，将中间模型绑定到 HABTM 关联模型.

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.

这篇关于使用 Containable 按关联模型 (HABTM) 上的条件过滤的分页结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！