CakePHP distinct picks extra column

Question

i'm working on a project in CakePHP but stuck at a strange problem. I'm writing a query:

    $brands = $this->Product->find('all', array(
            'fields'=> array('DISTINCT Product.brand as brand'),
            'order'=>'Product.brand ASC',
            'conditions'=> array('Product.subcategory_id'=>$subcategory_id)
    ));

It is picking Product.id along with Product.brand which I do not want.

The query it generates is:

SELECT DISTINCT `Product`.`brand`, `Product`.`id` FROM `ecom`.`products` AS `Product` LEFT JOIN `ecom`.`subcategories` AS `SubCategory` ON (`Product`.`subcategory_id` = `SubCategory`.`id`) LEFT JOIN `ecom`.`categories` AS `Category` ON (`Product`.`category_id` = `Category`.`id`) WHERE `Product`.`subcategory_id` = 13 ORDER BY `Product`.`brand` ASC

How can I skip Product.id from select?

Thanks

Have you seen this [question](http://stackoverflow.com/questions/1718482/using-distinct-in-a-cakephp-find-function)? — skywalker, Apr 07 '14 at 16:39

score 0 · Answer 1 · answered Apr 07 '14 at 16:46

Adding group solved my issue:

    $brands = $this->Product->find('all', array(
            'fields'=> array('DISTINCT Product.brand as brand'),
            'order'=>'Product.brand ASC',
            'conditions'=> array('Product.subcategory_id'=>$subcategory_id),
            'group' => 'brand'
    ));

score 0 · Answer 2 · answered Apr 07 '14 at 19:45

I think it's because cake needs the id to create the joins

try

$brands = $this->Product->find('all', array(
        'fields'=> array('DISTINCT Product.brand as brand'),
        'order'=>'Product.brand ASC',
        'conditions'=> array('Product.subcategory_id'=> $subcategory_id),
        'recursive' => -1
));

CakePHP distinct picks extra column

2 Answers2