CakePHP and SQL Server 2008, Group Not Working

Question

Whenever I do:

$this->Job->find('all', array(
        'group' => array('Job.some_field'),
        'recursive' => -1
    ));

I get:

SQL Error: Column 'jobs.id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

It works fine with MySQL, but with SQL Server 2008 it seems like the group is no longer working. How to fix it? Thanks in advance, SQL Guru

Answer 1

The request is converted to something like this:

SELECT  *
FROM    Job
GROUP BY
        some_field

This is an invalid request according to standards SQL, however it works MySQLdue to extensions MySQL GROUP BY.

You need to leave only grouped columns or aggregates in the sentence SELECT:

SELECT  some_field, COUNT(*)
FROM    Job
GROUP BY
        some_field

with something like this:

$this->Job->find('all', array(
        'fields' => array('Job.some_field', 'COUNT(*)'),
        'group' => array('Job.some_field'),
        'recursive' => -1
    ));