CakePHP: Select DISTINCT in ContainableBehaviour

Question

I am trying to select only distinct related model entries but it seems it doesn't work.

I have this:

$active_questions = $this->Question->find('all', array('conditions' => array('test_id' => $active_tests), 'fields' => array('answer_style_id'), 'contain' => array(
        'Answer' => array(
            'fields' => array('capital_category_id'),
            'CapitalCategory' => array(
                'fields' => array('id', 'DISTINCT capital_id', 'DISTINCT category_id', 'delete_flag'),
                'Capital' => array(
                    'fields' => array('id', 'delete_flag')
                ),
                'Category' => array(
                    'fields' => array('id', 'delete_flag')
                )
            )
        )
    )));

But Cake seems to automatically add the associated model key, even id I specified it with a DISTINCT keyword:

Query: SELECT `CapitalCategory`.`id`, DISTINCT `CapitalCategory`.`capital_id`, DISTINCT `CapitalCategory`.`category_id`, `CapitalCategory`.`delete_flag`, `CapitalCategory`.`capital_id`, `CapitalCategory`.`category_id` FROM `capital_categories` AS `CapitalCategory`   WHERE `CapitalCategory`.`id` = 217

How do I filter out only DISTINCT capitals or categories? For the current example, Cake returns 20 categories with the same id. I want only one to be returned.

Thank you.

did you ever find a solution to this? I'm struggling with the same thing — bowlerae, May 14 '13 at 14:20
you have to use custom query for this using $this->Model->query(); method — Yash, Nov 04 '15 at 06:40

score 0 · Answer 1 · answered May 13 '11 at 18:20

0

Off the top of my head, the following may work using the 'group' option

$active_questions = $this->Question->find(
  'all', 
  array(
    'conditions' => array('Question.test_id' => $active_tests), 
    'fields' => array('answer_style_id'), 
    'contain' => array(
      'Answer' => array(
        'fields' => array('capital_category_id'),
        'CapitalCategory' => array(
          'fields' => array('id', 'capital_id', 'category_id', 'delete_flag'),
          'group' => array('capital_id', 'category_id'),
          'Capital' => array(
            'fields' => array('id', 'delete_flag')
          ),
          'Category' => array(
            'fields' => array('id', 'delete_flag')
          )
        )
      )
    )
  )
);

answered May 13 '11 at 18:20

Matthew Nessworthy

1,428
10
19

Nope, it doesn't work. And as far as I know, the `group` doesn't work in the `contain`...Do you have any other idea? – linkyndy May 13 '11 at 19:04
Apologies for the late response. Without being able to debug it directly there's nothing that pops out at me. – Matthew Nessworthy May 26 '11 at 15:18
It's OK. I have read later that the `contain` key doesn't support **all** `find` keys, therefore the `group` or `DISTINCT` do not work. I was wondering whether there is another way to do this. – linkyndy May 26 '11 at 18:41
@AndreiHorak any solution to this? – Mubasshir Pawle May 29 '17 at 09:29
@MubasshirPawle no, and unfortunately I haven't played with PHP ever since. Feel free to post an answer to this question once you find it! – linkyndy May 30 '17 at 08:09

score 0 · Answer 2 · answered Jul 28 '11 at 02:34

0

Why don't you put 'group' just below 'fields' and not inside 'contain', also you may have to remove 'fields' altogether.

answered Jul 28 '11 at 02:34

jacktrade

21
3

1

Because I need to group the results from the contained models, not the model itself. – linkyndy Jul 28 '11 at 18:26

CakePHP: Select DISTINCT in ContainableBehaviour

2 Answers2