CakePHP 3.2 using counterCache Behavior to get an average

Question

I am trying to use counterCache behavior to update an average 'stars' rating from a Reviews table to the Profiles table it belongsTo.

Here is my Reviews Table:

class ReviewsTable extends Table
{
    public function initialize(array $config)
    {
        $this->table('reviews');
        $this->displayField('name');
        $this->primaryKey('id');
        $this->addBehavior('Timestamp');
        $this->addBehavior('Ceeram/Blame.Blame');
        $this->belongsTo('Profiles', [
            'foreignKey' => 'profile_id',
            'joinType' => 'INNER'
        ]);
        $this->addBehavior('CounterCache', [
            'Profiles' => [
                'rating_avg' => function($event, $entity, $table){
                    $reviews = $this->find();
                    $avg = $reviews->func()->avg('stars');
                    return $avg;
                }
            ]
        ]);
    }

Profiles table has a 'rating_avg' decimal(2,1) , and Reviews table has a 'stars' int column.

When I add a Review with an integer value for 'stars', I get a fatal error:

Error: [PDOException] SQLSTATE[HY000]: General error: 1111 Invalid use of group function

I don't know where the 'group' function is being used.

After the error, this is what is showing in DebugKit's SQL log:

INSERT INTO reviews (
  created, modified, created_by, profile_id, 
  content, stars, title
) 
VALUES 
  (
    '2016-03-11 17:17:35', '2016-03-11 17:17:35', 
    38, 1, '', 4, 'check him out'
  )

UPDATE 
  profiles 
SET 
  rating_avg = (
    AVG(stars)
  ) 
WHERE 
  id = 1

score 0 · Answer 1 · answered Sep 08 '16 at 08:21

It's because you have an array of data you need to add a where() and collections to get data..... here's an example that i use for a website.... change and use what you need

$this->addBehavior('CounterCache', [
            'Profiles' => [
                'rating_avg' => function ($event, $entity, $table) {


                    $response = $this->find()->where(['profile_id'=>$entity->profile_id])->ToArray();
                    $countratings=count($response);

                        $collection=$response;
                        $collection = new Collection($collection);
                        $sumrating=$collection->sumOf('stars');
                                if ($sumrating <> 0 && $countratings <> 0) {
           $RatePer5=(($sumrating/$countratings)*100)/5;
        }else{
            $RatePer5="0";
        }



                    return $RatePer5;


                }
            ]
        ]);

CakePHP 3.2 using counterCache Behavior to get an average

1 Answers1