GROUP and COUNT() ages in 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'
));

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
       开发者_JAVA技巧         )

        )

Surely there must be a better way.

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 

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

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

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

You would need to call $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

        )
    ...
)

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.

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

This would return (something like):

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

First, I don't think you need 'COUNT(id)'. You can always count that in PHP easily:

foreach($data as $group){echo count($group);}

To filter, check on the original field, not the derived one:

'conditions'=>array('User.dob >' => date('Y-m-d', strtotime('-20 years')))

Anyhow, there's always Model->query() if you need.

I think you are getting numeric indexes on your results array because the fields you are adding haven't been generated by CakePHP. CakePHP usually generates queries (and field names) more like this in SQL:

SELECT `Item`.`id`, `Item`.`name` FROM `items` AS `Item` WHERE 1 = 1 ORDER BY `Item`.`name` ASC

You should try and mimic CakePHP's field naming conventions when adding custom elements to your queries, if you want CakePHP to better understand and format the results coming back from MySQL:

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

Maybe this will give you more luck getting the conditions to work. :)

What version of cakephp are you using? I think earlier versions of 1.2 and I believe all versions of 1.1 had some issues with group by and complex aggregate queries -- it was later resolved. That said the results look like cake is running what you are asking it to: returning a list of the ages, and the count of the users with that age.

Regardless, why run that in mysql and tie up your db server? Get the raw values, and rewrite the query to calculate the aggregates in php, and append it to the returned array.

If you must use mysql, there is always $this->query(); For complex queries its often worth it to bypass cake, but as I said, the results make sense given the query.