CakePHP GROUP and COUNT items returned in list

Question

I know that there are some similar quesiton here, but they are all related to using

Model->find('all'); 

But this is not what I do, I do:

 Model->find('list'); 

This is what makes the difference between this worker and does not work.

---

Given a product group, I want to find all the brands in this group and the quantity of each brand.

It 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 tell him to give me an array, where the keys are Product.brand and the values ​​are COUNT(Product.brand)

I get the following:

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

When I expect this:

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

It works if I do all instead of list , but it just gives me a much more complex array that can be drilled. I use everything perfectly, I just wanted to know at first if there is a reason why it does not work on the list ?

Answer 1

Short Description : find ('list') has problems with aliases (and therefore combines functions like COUNT() , etc.), so CakePHP 1.3 should be used instead. CakePHP 1.2 uses an application .

Detailed : Most likely the problem is your

 COUNT(`Product.brand`) AS brand_count 

Because find('list') does

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

according to the results of the query, where $lst['valuePath'] will be

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

while the results would actually be "{n}.Product.brand_count" - does not 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'); 

Answer 2

In the controller in Paginator, you can use the following:

  $itemads = $this->paginate($itemads, ['contain' => ['Departments', 'Stores'],'group'=>'upc','fields'=>array("upc_count"=>"COUNT(`upc`)")]);