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

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

Answer 2

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.