CakePHP 中的 GROUP 和 COUNT() 年龄

2021-12-21 00:00:00 mysql cakephp

我正在尝试使用 CakePHP 按出生日期分组并根据结果进行计数.这是我的查询.

I'm trying to group by date of birth and count based on the results, using CakePHP. Here's my query.

$data = $this->User->find('all', array(
    'fields' => array(
        "DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(User.dob, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(User.dob, '00-%m-%d')) AS age",
        'COUNT(id)'
    ),
    'group' => 'age'
));

到目前为止，一切都很好.User.dob 字段是出生日期，它是一个 DATETIME 字段.

So far, so good. The field User.dob is date of birth, it's a DATETIME field.

事实是，它返回如下内容:

Thing is, it returns something like this:

Array
(
    [0] => Array
        (
            [0] => Array
                (
                    [age] => 9
                    [COUNT(id)] => 1
                )

        )

    [1] => Array
        (
            [0] => Array
                (
                    [age] => 10
                    [COUNT(id)] => 1
                )

        )

    [2] => Array
        (
            [0] => Array
                (
                    [age] => 11
                    [COUNT(id)] => 1
                )

        )

    [3] => Array
        (
            [0] => Array
                (
                    [age] => 12
                    [COUNT(id)] => 8
                )

        )

    [4] => Array
        (
            [0] => Array
                (
                    [age] => 13
                    [COUNT(id)] => 1
                )

        )

肯定有更好的方法.

我什至无法过滤它.此代码引发错误.未知列年龄"

And I can't even filter it. This code throws error. Unknown column 'age'

$data = $this->User->find('all', array(
    'conditions' => array('age >' => 20),
    'fields' => array(
        "DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(User.dob, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(User.dob, '00-%m-%d')) AS age",
        'COUNT(id)'
    ),
    'group' => 'age'
));

顺便说一下，这些是查询.

By the way, these are the queries.

SELECT DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(User.dob, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(User.dob, '00-%m-%d')) AS age, COUNT(id) FROM `users` AS `User` WHERE 1 = 1 GROUP BY age

(年龄计算例程可在 matt 的博客中找到.)

(The age calculation routine was found in matt's blog.)

推荐答案

您得到的结果几乎是 CakePHP 产生的最好的结果.

The results you get are pretty much the best that CakePHP produces.

为了简化这个，你应该使用 Set::combine 重新索引你的数组.

To simplify this you should use Set::combine which re-indexes your array.

您需要调用 $data = Set::combine($data, '{n}.0.age', '{n}.0.COUNT(id)');

这将返回一个以年龄为索引并计数为值的数组:

This would return an array with age as the index and count as the value:

Array
(
    [9] => Array
        (
            [COUNT(id)] => 1

        )

    [10] => Array
        (
            [COUNT(id)] => 1

        )
    ...
)

数组中额外深度的原因是，如果你不使用计算字段，cake会使用model作为内部数组的key，这样你就可以放入多个model作为字段，它们会被拆分成不同的数组.当您使用计算字段时，它保持相同的结构，但不知道模型，因此必须将其放入通用数组中.

The reason for the extra depth in the array is that cake uses the model as the key for the inner array if you are not using calculated fields, so that you can put in multiple models as fields and they will be split into different arrays. When you use calculated fields it keeps the same structure, but doesn't know the model so has to put it in a general array.

假设您也想按男性/女性分组，并且您有一个 User.sex 字段，该字段不是计算字段.

So lets say you want to group by male/female as well and you have a User.sex field, which is not a calculated field.

$data = $this->User->find('all', array(
    'fields' => array(
        "User.sex"
        "DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(User.dob, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(User.dob, '00-%m-%d')) AS age",
        'COUNT(User.id) AS [count]' // n.b. you have to give aliases normally
    ),
    'group' => 'age', 'User.sex'
));

这将返回(类似):

Array
(
    [0] => Array
        (
            [User] => Array
                (
                    [sex] => Male
                )
            [0] => Array
                (
                    [age] => 4
                    [count] => 1
                )

        )

    [1] => Array
        (
            [User] => Array
                (
                    [sex] => Female
                )
            [0] => Array
                (
                    [age] => 10
                    [count] => 1
                )

        )

    [2] => Array
        (
            [User] => Array
                (
                    [sex] => Male
                )
            [0] => Array
                (
                    [age] => 10
                    [count] => 1
                )

        )
)

因此为了一致性，即使您只使用计算字段，额外的深度也始终存在

Thus for consistency the extra depth is always there even if you only use calculated fields

相关文章