0

I am looking to do the same thing as this using cakephp:

https://stackoverflow.com/a/6127471

    $eachRWithCode = $this->MovieReview->find( 'all', array(
    'fields' => 'MovieReview.*',
    'group' => array('MovieReview.code'),
    'recursive' => '-1',
    'nofilter' => true,
    'order' => 'MovieReview.code'
    ));

This code gives an error:

ERROR: column "MovieReview.id" must appear in the GROUP BY clause or be used in an aggregate function

Community
  • 1
  • 1
Bryan
  • 17,201
  • 24
  • 97
  • 123

1 Answers1

0

what DBMS are you using? Mysql let you show columns that you don't use in aggregate function (but you are not , but ather DBMS don't. So every column you want to show you have to put it in a aggregate function such as MAX() or SUM() or COUNT()

so you have to do something like (assuming you have a score column):

$eachRWithCode = $this->MovieReview->find( 'all', array(
    'fields' => array(
        'COUNT(MovieReview.id)',
        'AVG(MovieReview.score),
        'MAX(MovieReview.code)
     ),
    'group' => array('MovieReview.code'),
    'recursive' => '-1',
    'nofilter' => true,
    'order' => 'MovieReview.code'
    ));
arilia
  • 9,373
  • 2
  • 20
  • 44
  • I am using PSQL. That makes sense. Pretty sure I wouldn't need to use MAX for MAX(MovieReview.code) because it is in the group by clause. Is it possible to take the field values from a single record matching the group? – Bryan Feb 18 '14 at 17:31
  • please explain what you mean – arilia Feb 18 '14 at 21:11