分页结果按相关模型（HABTM）条件筛选，使用Containable [英] Paginate results filtered by condition on associated model (HABTM) using Containable

问题描述

我需要列出属于特定类别（HABTM协会）的产品。

在我的产品模型中我有

  var $ actsAs = array（'Containable'）; 
 var $ hasAndBelongsToMany = array（
'Category'=> array（
'joinTable'=>'products_categories'
）
）; 
  

并在 ProductsController

  $ this-> paginate = array（
'limit'=> 20，
'order'=> array（'Product.name'=>'ASC'），
'contains'=> array（
'Category'=& 'conditions'=> array（
'Category.id'=> 3 
）
）
）
）; 
 $ this-> set（'products'，$ this-> paginate（））; 
  

但是，结果SQL看起来像这样：

< pre b'''''''b'b'''''b''''''''''''
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 ）

而我需要

  SELECT COUNT（*）AS`count` 
从`products` AS`产品`
 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; 
  

（即选择前20个产品到类别与 id = 3）

注意：
没有可容纳的可能解决方案是使用连接（如 Dave 建议）。
此帖子提供了一个非常方便的帮助程序来构建 $ this-> paginate ['joins'] 以通过HABTM协会分页。

注意：仍然寻找更优雅的解决方案使用可容纳比假 hasOne 绑定。

解决方案

最后，我找到了一种方法来做我想要的，答案：

在<$ c中强制 JOIN （并能够通过关联模型的条件进行过滤） $ c>可容纳 - 您必须使用fake hasOne 关联。

在我的例子中，代码 ProductsController 应为：

  $ this-> Product-> bindModel（array（'hasOne'= > array（'ProductsCategory'）），false）; 
 
 $ this-> paginate = array（
'limit'=> 20，
'order'=> array（'Product.name'=& ASC'），
'conditions'=> array（
'ProductsCategory.category_id'=> $ category 
），
'contains'=>'ProductsCategory'
）; 
 
 $ this-> set（'products'，$ this-> paginate（））; 
  

注意 false code> bindModel - 这使绑定持久化。这是因为在之前 find（'） paginate（） all'），这将重置临时绑定。因此，您可能希望之后手动 unbindModel 。

此外，如果您的条件在HABTM关联模型中包含多个ID，您可能需要添加'group'=> 'product.id'加入您的 $ this-> paginate [] （如 Aziz ）

UPDATE：
然而，这种方法有一个严重的缺点to join方法（由 Dave 建议）：condition只能应用于中间模型的外键（ category_id in my case）;如果你想在相关模型的任何其他字段上使用条件 - 你可能需要添加另一个 bindModel（'hasOne'），将中间模型绑定到HABTM关联模型。

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.

解决方案

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.

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