CODEWITHSUNDEEP
mysqlcakephpcakephp-3.0
C0ol_Cod3r
C0ol_Cod3r

Reputation: 949

CakePHP 3, Count / Group Find Result?

UPDATE

I have been playing around with pure SQL in phpMyAdmin, and think I have an answer, I would like someone to give it a look over to see if there are any problems with the query itself.

Also I am not sure what the best way to proceed is, I know with Doctrine (Symfony) its easy to drop down to run an SQL query. I know it should be possible to do so with CakePHP, but would it be better to build the query with the query builder?

SQL Query :

 SELECT userlevel_id AS userlevelID, COUNT( * ) AS count
 FROM users
 WHERE account_id = 38 <-var id here
 GROUP BY userlevel_id

So I am using CakePHP 3.X, and want to group/count each users level by account, so this is what I 'was' doing,

For example, I had three of these find calls, one for admins, managers and users.

  $CountManagers = $UsersTable->find('all') //Get current total of managers
                                ->where(['Users.account_id' => $AccountID, 'Userlevels.userlevel' => 'Manager'])
                                ->contain(['Userlevels'])
                                ->count();

I would then build up the array I would need for each of the users level, after counting if the 'account' level had more/less than what the account level had set.

For example, 

  if ($CountAdmins < $PackageAdmins) { $ListAccessLevels['2'] = 'Admin'; }
  if ($CountManagers < $PackageManagers) { $ListAccessLevels['3'] = 'Manager'; }
  if ($CountUsers < $PackageUsers) { $ListAccessLevels['4'] = 'User'; }

But I know this is wrong, and want to have only one find call, and get it to return the array for me.

There are about 4 different levels for this system, each with a number of Users, Managers and Admins for each (some of which are set to NULL, for unlimited).

So this is what I have done so far,

  $GetTest = $UsersTable->find('all')
                                 ->where(['account_id' => $AccountID*])
                                 ->contain(['Userlevels'])
                                 ->toArray();

*This returns all the users for all the current users, $AccountID gets the current AccountID of the logged in user.

So I get a list of six users, 1 Admin, 1 Manager and 4 Users. (Test Account). When I add group, like,

  $GetTest = $UsersTable->find('all')
                                 ->where(['account_id' => $AccountID*])
                                 ->group('userlevel_id')
                                 ->contain(['Userlevels'])
                                 ->count();

This returns 3, not what I need! So I was looking at a sub query, and then count each userlevel_id but I am not sure how to do that?

Thanks,

Upvotes: 3

Views: 14792

Answers (1)

C0ol_Cod3r
C0ol_Cod3r

Reputation: 949

I believe that this is the solution, needs more work but seems to, from lots of trail and error, give me the same result in CakePHP as the SQL (see above) that I run in phpMyAdmin.

$Query = $UsersTable->find('all');

$Query->select([ 
              'userlevel_id',
              'count' => $Query->func()->count('*')
            ])
     ->where(['account_id' => $AccountID])
     ->group('userlevel_id');

foreach ($Query as $row) {
   debug($row);
}

Upvotes: 13

Related Questions