CakePHP GROUP and COUNT items returned in list

Question

I know there are some similar quesiton like this on here but they are all about when using

Model->find('all');

But thats not what I am doing, I am doing:

Model->find('list');

Which is whats making the difference between this working and not working.

---

Given a group of Products I want to find all the brands in that group and how many of each brand.

Sounds simple enough, here is what I did:

$fields = array('Product.brand','COUNT(`Product`.`brand`) AS brand_count')
$brand_data = $this->Product->find('list',array(
    'fields'=>$fields,
    'conditions'=>$conditions,
    'recursive'=>0,
    'group' => 'Product.brand'
));
debug($brand_data);

In this I am telling it to give me an array where the Keys are Product.brand and the values are COUNT(Product.brand)

I am getting this:

Array
(
    [Brand A] => 
    [Brand B] => 
    [Brand C] =>  
)

When I am expected this:

Array
(
    [Brand A] => 534
    [Brand B] => 243
    [Brand C] => 172
)

It works if I do all instead of list though, it just gives me a much more complicated array to drill through. I am fine using all, I just first wanted to see if there was a reason why its not working in list?

Answer 1

Brief recap: find('list') has problems with aliased fields (and therefore aggregate functions like COUNT() etc.), so you should use CakePHP's 1.3 instead. For CakePHP 1.2 uses there's a contraption.

Detailed: Most likely problem lies in your

COUNT(`Product.brand`) AS brand_count

Because find('list') does

Set::combine($results, $lst['keyPath'], $lst['valuePath'], $lst['groupPath']) 

on results of query, where $lst['valuePath'] would be

"{n}.COUNT(`Product`.`brand`) AS brand_count"

while in results it would be actually "{n}.Product.brand_count" - doesn't line up.

Try making your COUNT() a virtual field.

I.e.:

//model
var $virtualFields = array(
    'brand_count' => 'COUNT(Product.brand)'
);

//controller
$fields = array('Product.brand','Product.brand_count');