Statistical Query and AVG

Archives Yii 1.0.x Discussions
1

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

have:


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

need:


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

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


Yii::app()->avgRating
3

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.

4

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

5

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

6

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.

7

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?

8

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.