cakePHP - how do i get the items count for a related model?

Question

The models are: stores and product, and they are associated by:

var $belongsTo = array(
  'Store' => array(
    'className' => 'Store',
    'foreignKey' => 'store_id'
        ))

var $hasMany = array(
  'Product' => array(
'className' => 'Product',
'foreignKey' => 'store_id'
    ))

I want to get a list of all stores and the count of products they have. How should I modify the call: $this->Store->find('all', array(<..some conditions..>)) to return that type of data?

Answer 1

One method is to use Cake's built-in counterCache option in your association. This is probably the most performant option, though it does require adding a field to your table.

In your stores table, add an INT field called product_count

In your Product model add the counterCache option to your association:

var $belongsTo = array(
    'Store' => array(
    'className' => 'Store',
    'foreignKey' => 'store_id',
    'counterCache' => true
 ));

Whenever you add or delete Product records, it will automatically update the product_count field of the associated Store record, so that there is no need to alter your find operations.

Note that if you choose this route, you will need to manually update the product_count field for the initial value to be correct, as it only updates after add/delete operations.

Answer 2

I believe something like the following would work, however I cannot test it from here. The COUNT() contents might need tweaking to work with how Cake constructs its queries.

$this->Store->virtualFields = array('product_count' => 'COUNT(Product.id)');
$this->Store->find('all', array(
    'fields' => array('Store.id', 'Store.product_count'),
    'group' => array('Store.id'),
));

Answer 3

After CakePHP 2.0 you can also add conditions to your count and multiple counters per model.

Add any integer field to your Store then use this in your Product model:

var $belongsTo = array(
    'Store' => array(
        'className' => 'Store',
        'foreignKey' => 'store_id',
        'counterCache' => array(
            'anyfield', array('Product.id >' => 0),
            'stock' => array('Product.status' => 'stock')
        )
    )
);