Statistical Query and AVG

I have a table User, there is rating column

I need to count avg rating for all users with stat query

if I’m using relation like this

'avgRating' => array(self::STAT,'User','id','select'=>'avg(rating)')

, then in query I will have: having id = curent user id, but I need it fow all users


SELECT `id` AS `c`, avg(rating) AS `s` FROM `User` GROUP BY `id` HAVING `User`.`id`=1


SELECT avg(rating) AS `s` FROM `User`

It will be ideal, if this value can be accesed with something like this


Extend CWebApplication with getAvgRating(), so you can use the latter approach.

Please note that using ActiveRecord in this scenario is not recommended.

Use CDbCommandBuilder to obtain this value.

Thanx, it’s because of perfomance, I guess?

Hmm, I think it can be added in main.php like component.

Yes, it can be added as a component, but that way you have call the component’s method, like Yii::app()->ratingComponent->avgRating.

Using CDbCommandBuilder is recommended not because of performance, but because this retrieved data does not follow the AR schema very well.

Thank you a lot.

I jusk add to my User model public method:

	public function avgRating()


		$criteria=new CDbCriteria(array(

			'select'=>'avg(rating) as avgRating',


		$rows=$this->dbConnection->commandBuilder->createFindCommand($this->tableSchema, $criteria)->queryAll();


		return $rows[0]['avgRating'];


Is that way good?

Yes this is correct.

If I were you, I’d change the following details:

  • modify function so it’s static; this way it could be used as User::avgRating(). I don’t think this is so important, as default Yii methods are not static as well,

  • I’d pass the query, so it’d be nothing fancy ($this->dbConnection->createCommand(‘SELECT avg(rating) AS avgRating FROM User’)),

  • I’d call queryScalar() instead of queryAll(), so you don’t have to worry about row numbers or field names.