Group By within contain cakephp

Question

Hellow , I want to use group by within contain in cakephp. In the following case i want to take only distinct organization within organizationUser array..

         $options = array(
        'conditions' => array('User.' .$this->User->primaryKey => $userId), 
        'contain' => array(
                        'OrganizationUser'=>array(
                            'conditions'=>['status'=>3],
                            'group'=> array( 'OrganizationUser.organization_id')),
                        'OrganizationUser.Organization',
                        'OrganizationUser.Organization.Noticeboard',
                        'OrganizationUser.Organization.Newsboard',
                        'OrganizationUser.Organization.Noticeboard.Branch',
                    ),
        'page'=>$page,
        'limit'=>$limit
        );



        $org = $this->User->find('all', $options);

But this is throwing error like 'Column not found', and 'conditions' is working fine within OrganizationUser but 'group' not working.I am using cakephp version 2.Thanks in advance.

Manohar Khadka · Accepted Answer · 2016-05-23T09:32:30.033

2

I don't think cakephp 2+ offer something like you are doing to make field distinct within contain. So better to try following.. Replace :

'group'=> array( 'OrganizationUser.organization_id')

By

'fields'=> array( 'DISTINCT OrganizationUser.organization_id')

that might work for you.

edited May 23 '16 at 09:32

answered May 23 '16 at 08:37

Manohar Khadka

2,186
2
18
30

1

Well, an alternative approach with better control would be to use `joins` with query builder... I'd post a new answer but since the issue was addressed in cake 3.0, I don't think it deserves anything more than a comment on this clever solution. – Fr0zenFyr Sep 11 '17 at 09:10

score 0 · Answer 2 · answered Dec 18 '20 at 03:53

In my case, I'm using cake version 4+.

In my table the relation I've made

$this->belongsTo('CreatedOperator')
      ->setClassName(USERS_PLUGIN . '.Users')
      ->setForeignKey('created_by')
      ->setJoinType('INNER')
   ;

and I'm calling the relation like

       $query
        ->disableHydration()
        ->select([
            'CreatedOperator.id',
            'CreatedOperator.first_name',
            'CreatedOperator.last_name',
            'full_name' => $query->func()->concat(['CreatedOperator.first_name' => 'identifier',' ','CreatedOperator.last_name' => 'identifier']),
            'total' => $query->func()->count('CreatedOperator.id')])
        ->contain(['CreatedOperator'])
        ->group(['CreatedOperator.id'])
        ;

    return $query->toList();

Group By within contain cakephp

2 Answers2

Linked