CakePHP 3，计数/组查找结果？ [英] CakePHP 3, Count / Group Find Result?

问题描述

更新

我一直在phpMyAdmin中使用纯SQL，我想我有一个答案，我希望有人给出查看一下查询本身是否存在任何问题。

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.

我也不知道最好的处理方法是什么，我知道使用Doctrine（Symfony）可以轻松地运行SQL查询。我知道使用CakePHP可以做到这一点，但是使用查询生成器来构建查询会更好吗？

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查询：

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

---

所以我正在使用CakePHP 3.X，并且想按帐户对每个用户级别进行分组/计数，所以这就是我正在做的事情，例如，

---

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.

例如，

  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.

此系统大约有4个不同的级别，每个级别都有多个User，Manager和Admins（其中一些设置为NULL，表示无限制）。

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();

*这将返回所有当前用户的所有用户， $ AccountID 获取已登录用户的当前AccountID。

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

所以我得到了六个用户的列表，一个是Admin，一个是Manager，四个是用​​户。 （测试帐户）。当我添加组时，例如，

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();

这将返回3，而不是我所需要的！所以我在看一个子查询，然后计算每个 userlevel_id ，但是我不确定该怎么做？

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?

谢谢

• 如果我未包含任何需要的内容，请告诉我，我将更新我的问题...

推荐答案

我相信这是解决方案，需要做更多的工作但是似乎从很多的错误中发现，在CakePHP中给我的结果与在phpMyAdmin中运行的SQL相同（见上文）。

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);
}

这篇关于CakePHP 3，计数/组查找结果？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！