How to count duplicate values in cakephp?

Question

I need to find out how many times each value is duplicated. Here's what I've got so far. I'd like to use it for counting votes in polls.

$this->set('votes', $this->Answer->Vote->find('all', array(
                    'fields' => array('Vote.answer_id'),
                    'group' => array('Vote.answer_id HAVING COUNT(*) > 1'))));

And it returns me which values are duplicated, like this:

1st answer
2nd answer
4th answer

But I still need the number, to show how many times it's duplicated. Something like this.

1st answer (5)
2nd answer (3)
3rd answer (1) // not duplicated
4th answer (8)

EDIT: Solution that worked for me

In controller:

$this->set('votes', $this->Answer->Vote->find('all', array(
       'fields' => array('Vote.answer_id', 'count(*) as TotalVotes'),
       'group' => array('Vote.answer_id HAVING COUNT(*) >= 1'))));

In view:

foreach ($votes as $vote):
echo $vote[0]['TotalVotes'];
endforeach;

Answer 1

This also works:

   $this->set('votes', $this->Answer->Vote->find('all', array(
       'fields' => array(
           '*',
           '(SELECT COUNT(*) FROM votes WHERE answer_id = Vote.answer_id) AS `count`'),
       'group' => array('Vote.answer_id HAVING COUNT(*) >= 1')
                                )));

Answer 2

This query will work.

SELECT answer_id, COUNT(*) AS TotalVotes FROM votes GROUP BY answer_id HAVING COUNT(*) > 1

CakePHP equivalent find condition:

$result = $this->Answer->Vote->find('all', array(
                'fields' => array('Vote.answer_id', 'count(*) as TotalVotes'),
                'group' => array('Vote.answer_id HAVING COUNT(*) > 1')));

You will find TotalVotes in a separate array at index [0]. To overcome this issue, just write the following code before your select query:

$this->Answer->Vote->virtualFields['TotalVotes'] = 0;

Answer 3

    public function admin_duplicate_account() {
    $this->User->unBindModel(array('hasOne' => array('Admin')));

    if ($this->request->is('post') || $this->request->is('put')) {
        // pr($this->request->data); exit;
        if ($this->request->data['User']['email'] == 1) {
            $arr_having = array();
            $arr_having['fields'] = 'User.email';
            $arr_having['group'] = 'User.email HAVING COUNT(*) > 1';
            $regs = $this->User->find('all', $arr_having, array('conditions' => array('User.role' => array('U', 'P'), 'User.user_status' => array('active', 'lead', 'inactive'))));
            $names = array();
            foreach ($regs as $reg) {
                if ($reg['User']['email']) {
                    $names[] = $reg['User']['email'];
                }
            }

            $this->set('allusers', $this->User->find('all', array(
                        'conditions' => array('User.email' => $names, 'User.role' => array('U', 'P'), 'User.user_status' => array('active', 'lead', 'inactive')))));
        }


        if ($this->request->data['User']['first_name'] == 1) {
            $arr_having = array();
            $arr_having['fields'] = 'PersonalInformation.first_name';
            $arr_having['group'] = 'PersonalInformation.first_name HAVING COUNT(*) > 1';

            $regs = $this->User->find('all', $arr_having, array('conditions' => array('User.role' => array('U', 'P'), 'User.user_status' => array('active', 'lead', 'inactive'))));

            //pr($regs); exit;
            $names = array();
            foreach ($regs as $reg) {

                if ($reg['PersonalInformation']['first_name']) {
                    $names[] = $reg['PersonalInformation']['first_name'];
                }
                //$names[] = $reg['PersonalInformation']['first_name'];
            }
            //pr($names); 
            $data = $this->User->find('all', array(
                'conditions' => array('PersonalInformation.first_name' => $names, 'User.role' => array('U', 'P'), 'User.user_status' => array('active', 'lead', 'inactive'))));
            //pr($data); exit;

            $this->set('allusers', $data);
        }



        if ($this->request->data['User']['last_name'] == 1) {

            $arr_having = array();
            $arr_having['fields'] = 'PersonalInformation.last_name';
            $arr_having['group'] = 'PersonalInformation.last_name HAVING COUNT(*) > 1';

            $regs = $this->User->find('all', $arr_having, array('conditions' => array('User.role' => array('U', 'P'), 'User.user_status' => array('active', 'lead', 'inactive'))));

            //pr($regs); exit;
            $names = array();
            foreach ($regs as $reg) {

                if ($reg['PersonalInformation']['last_name']) {
                    $names[] = $reg['PersonalInformation']['last_name'];
                }
                //$names[] = $reg['PersonalInformation']['first_name'];
            }
            //pr($names); 
            $data = $this->User->find('all', array(
                'conditions' => array('PersonalInformation.last_name' => $names, 'User.role' => array('U', 'P'), 'User.user_status' => array('active', 'lead', 'inactive'))));
            //pr($data); exit;

            $this->set('allusers', $data);
        }


        if ($this->request->data['User']['primary_phone'] == 1) {

            $arr_having = array();
            $arr_having['fields'] = 'PersonalInformation.primary_phone';
            $arr_having['group'] = 'PersonalInformation.primary_phone HAVING COUNT(*) > 1';

            $regs = $this->User->find('all', $arr_having, array('conditions' => array('User.role' => array('U', 'P'), 'User.user_status' => array('active', 'lead', 'inactive'))));

            //pr($regs); exit;
            $names = array();
            foreach ($regs as $reg) {

                if ($reg['PersonalInformation']['primary_phone']) {
                    $names[] = $reg['PersonalInformation']['primary_phone'];
                }
            }
            //pr($names); 
            $data = $this->User->find('all', array(
                'conditions' => array('PersonalInformation.primary_phone' => $names, 'User.role' => array('U', 'P'), 'User.user_status' => array('active', 'lead', 'inactive'))));
            //pr($data); exit;

            $this->set('allusers', $data);
        }
        if ($this->request->data['User']['dob'] == 1) {


            $arr_having = array();
            $arr_having['fields'] = 'PersonalInformation.dob';
            $arr_having['group'] = 'PersonalInformation.dob HAVING COUNT(*) > 1';

            $regs = $this->User->find('all', $arr_having, array('conditions' => array('User.role' => array('U', 'P'), 'User.user_status' => array('active', 'lead', 'inactive'))));

            //pr($regs); exit;
            $names = array();
            foreach ($regs as $reg) {

                if ($reg['PersonalInformation']['dob']) {
                    $names[] = $reg['PersonalInformation']['dob'];
                }
            }
            //pr($names); 
            $data = $this->User->find('all', array(
                'conditions' => array('PersonalInformation.dob' => $names, 'User.role' => array('U', 'P'), 'User.user_status' => array('active', 'lead', 'inactive'))));
            //pr($data); exit;

            $this->set('allusers', $data);
        }
    } else {

        $this->set('allusers', $this->User->find('all', array(
                    'conditions' => array('User.role' => array('U', 'P'), 'User.user_status' => array('active', 'lead', 'inactive')))));
    }

    $this->layout = 'admin';
}

Answer 4

Here you can search Groups of records from multiple duplicate records

  public function admin_duplicate_account() {
        $this->User->unBindModel(array('hasOne' => array('Admin')));
        $condition = array(
            'User.role' => array('U', 'P'),
            'User.user_status' => array('active', 'lead', 'inactive'),
        );
         $arr_group = $arr_having = array();

        if ($this->request->is('post') || $this->request->is('put')) {
            //pr($this->request->data); exit;

            if ($this->request->data['User']['email'] == 1) {
                $arr_group[] = 'User.email';
                $arr_having[] = 'count(User.email) > 1';
            }

            if ($this->request->data['User']['first_name'] == 1) {
                $arr_group[] = 'PersonalInformation.first_name';
                $arr_having[] = 'count(PersonalInformation.first_name) > 1';
            }
            if ($this->request->data['User']['last_name'] == 1) {
                $arr_group[] = 'PersonalInformation.last_name';
                $arr_having[] = 'count(PersonalInformation.last_name) > 1';
            }



            if ($this->request->data['User']['primary_phone'] == 1) {
                $arr_group[] = 'PersonalInformation.primary_phone';
                $arr_having[] = 'count(PersonalInformation.primary_phone) > 1';
            }

            if ($this->request->data['User']['dob'] == 1) {
                $arr_group[] = 'PersonalInformation.dob';
                $arr_having[] = 'count(PersonalInformation.dob) > 1';
            }
        }
//       pr($arr_group); 
//        pr($arr_having);  exit;
        if ($arr_group) {
            $arr_group = implode(' , ', $arr_group);
            $arr_having = implode(' && ', $arr_having);
            $group = array("$arr_group HAVING $arr_having");
        } else {
            $group = array();
        }
        $data = $this->User->find('all', array(
//            'fields'=>'count(User.email)',
            'conditions' => $condition,
//            'fields' => 'count(User.email,PersonalInformation.first_name, PersonalInformation.last_name,PersonalInformation.primary_phone,PersonalInformation.dob)',
//            'group' => array('User.email, PersonalInformation.first_name, PersonalInformation.last_name,PersonalInformation.primary_phone,PersonalInformation.dob HAVING count(User.email) > 1 &&  count(PersonalInformation.first_name) > 1 &&  count(PersonalInformation.last_name) > 1 &&  count(PersonalInformation.dob &&  count(PersonalInformation.primary_phone))')
            'group' => $group
        ));
        //pr($data); exit;
        $this->set('allusers', $data);
        $this->layout = 'admin';
    }