CakePHP containable HABTM limit

Question

According to the second sentence on the Containable Behavior section of the Cookbook, contain() supports the limit clause.

A new addition to the CakePHP 1.2 core is the ContainableBehavior. This model behavior allows you to filter and limit model find operations.

I'm Using CakePHP 2.5.

I have two models: Product and Category (HABTM relation).

The relations for Product:

public $hasAndBelongsToMany = array(
    'Category' => array(
        'className' => 'Category',
        'joinTable' => 'categories_products',
        'foreignKey' => 'product_id',
        'associationForeignKey' => 'category_id',
    ),
);

The relations for Category:

public $hasAndBelongsToMany = array(
    'Product' => array(
        'className' => 'Product',
        'joinTable' => 'categories_products',
        'foreignKey' => 'category_id',
        'associationForeignKey' => 'product_id',
    ),
);

I’m trying do use limit as follows:

$cats = $this->Category->find('all', array(
    'fields' => array(
        'id',
        'parent_id'
    ),
    'contain' => array(
        'Product' => array(
            'fields' => array(
                'id',
                'name',
            ),
            'limit' => 3,
            'order' => array('created DESC'),
        ),
    ),
));

The result is that overall only 3 products are selected. Not 3 product per category. The containable behavior is properly loaded. How do I get 3 products per category?

Inigo Flores Inigo Flores · Accepted Answer · 2016-01-09T12:52:57

Unfortunately you cannot use 'limit' on HABTM relationships. For performance reasons, CakePHP builds a single query to fetch all the HABTM records, which are later recursively assigned to the parent record key in the output array.

If you apply a LIMIT clause, it will result in CakePHP only fetching a limited number of associated Products, as you have noticed.

The HABTM section in the Cookbook should probably be amended to remove 'limit' as an option, or clarify its intended use.

See this answer for further information:

CakePHP and HABTM Model Limit Error

CakePHP containable HABTM limit

1 Answers