GROUP and COUNT() ages in CakePHP

Question

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
                )

        )

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.)

Answer 1

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

Answer 2

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.

Answer 3

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. :)