1

I am using cakephp2.7 and need to get distinct values from a table 

$rows = $this->Skill->find('list',array(
            'conditions'=>array(
                    'Skill.status'=>1,
                    'Skill.percent >='=> 70,
                    'Skill.percent <='=> 100,
                ),
            'fields' => array('DISTINCT Skill.row AS row'),
            //'fields' => 'DISTINCT Skill.row AS row',/*also tried*/
        ));

but it is giving mysql error ...near DISTINCT....
generated query is as below

SELECT `Skill`.`id`, DISTINCT `Skill`.`row` AS `row` FROM `mydb`.`skills` AS `Skill` WHERE `Skill`.`status` = 1 AND `Skill`.`percent` >= 70 AND `Skill`.`percent` <= 100

i tried same query in mysql giving same error but when I remove id column in mysql query it is running fine.

now issue is that skill.id column is added in query automatically by cakephp and it is primary key how to prevent including id column in find()?

alamnaryab
  • 1,480
  • 3
  • 19
  • 31
  • Have you tried GROUP BY skill.row instead of using select distinct? It should give you the same result without the issue of DISTINCT. – Lelio Faieta Sep 28 '15 at 19:13
  • I have added `'group'=>array('Skill.row'),` and removed distinct keyword but it wrong result – alamnaryab Sep 28 '15 at 19:28
  • Can you update your question with the new query and the array you get? Ps. I am not so familiar with cake php but you should get a query that in the end has group by skill.row without involving any array – Lelio Faieta Sep 28 '15 at 19:33
  • You need to describe the output you want (e.g., the format of `find`'s returned array.) Why are you using the `'list'` option that `find`? I would suggest you simply change the `'list'` to `'all'` in the `find`. – AgRizzo Sep 28 '15 at 19:37
  • yes @Agrizzo replacing `list` with `all` worked but values are too nested, I want to have as `list` – alamnaryab Sep 28 '15 at 19:40
  • I don't know what "too nested" means. You need to show the working code, how the result is formatted AND what you want the result to look like (within your question above.) – AgRizzo Sep 28 '15 at 19:55
  • 1
    possible duplicate of [Using DISTINCT in a CakePHP find function](http://stackoverflow.com/questions/1718482/using-distinct-in-a-cakephp-find-function) – Timber Sep 28 '15 at 20:58

0 Answers0