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?

Greensward

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.

Winkelman

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'),
));

Discounter

Answer 3

Check out find('count').

However, this may not scale well if your looking to pair this data with store data (see the answer by therealtomrose).

I've had trouble using find('count') and grouping data. Unfortunately this is one of those edge cases where frameworks writing queries for you fails.

Roundhead

Answer 4

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

Natividadnativism

Answer 5

A simple find all will fetch the data you need:

$Stores = $this->Store->find('all');

The count of products at one of the stores can be returned with the following code where '0' can be replaced with the number of the store's array index:

count($Stores[0]['Products']); //returns an integer

If you want the count of products at each store, you could consider looping over all stores thus:

foreach ($Stores as $Store) {
    echo count($Store['Products']);
}

Assumptions I am making:

1. You want the count of products at each store, not product count overall.
2. You're not too concerned with performance. If you have more than 50,000 records or so, you might want to consider pairing this back, but it will be considerably more complicated.

Ripuarian