[SOLVED] Simple 'select' with AVG

Yii 1.1 General Discussion
1

Hi folks,

I have a really easy SQL statement:

[indent]


SELECT AVG(rating) FROM `table` WHERE `value`= 156

[/indent]Actually I work with AR, the simple way to access the database.

without ‘AVG’ the select part of ‘CDbCriteria’ is absolut easy:

[indent]


$crit->select = 'rating';

[/indent]That work’s fine. And now I only want to add the ‘AVG’ SQL statement

Adding ‘AVG’ , the result is wrong:

[indent]


$crit->select = 'AVG(rating)';

[/indent]Is it really neccesary to use a Statistical Query as descriped in the guide?

I can’t believe it, because the SQL statement is so sipmple …

Thanx for comments.

Scryii

[indent] [/indent]

2

You need a model attribute that will hold the value of AVG() and use an alias in your SQL statement:




public $avg;

...

'SELECT AVG(rating) as `avg`...'
3

Hi Mike,

thank you for your comment.

I added the variable like descriped:

[indent]


 public $avg;

[/indent]and also within the select criteria:

[indent]


$crit->select = "AVG(rating) as 'avg'";

[/indent]When I copy the SQL - Statement from the trace console it looks like:

[indent]


SELECT AVG(rating) as 'avg' FROM `table` `t` WHERE value = 156 LIMIT 1

[/indent]and get the result in the mysql frontend:

[indent]


AVG



4,6667

[/indent]That’s fine & correct.

But I have problems to get the result in my code.

Now I show you the code, maybe I’m wrong in an other line …

[indent]




$bw = new CBewertung(); // my class

$crit = new CDbCriteria();

		

$crit->condition = 'produkt_id = :pid AND anleitung = :anl';

$crit->params = array(':pid'=>$id, ':anl'=>$typ);

		

 $crit->select = "AVG(stars) as 'avg'"; 

 $bewertung = $bw->find($crit);

 echo $bewertung['stars'];

[/indent]In this case echo is empty.

Changing the select-stament:

[indent]




 $crit->select = "stars";

[/indent]The result is 4 (that’s right, because the first value in the db is 4.)

Hope that helps to explain my challenge …

[indent] [/indent]

4

Have you tried setting the select to a CDbExpression?




$crit->select = new CDbExpression( 'AVG( rating )' );
5

Yepp.

Same think: SQL stament (trace mode) looks perfect and works with mysql frontend.

But my code echo is still empty

:(

There must be a ‘stupid’ mistake …

6

You’re selecting AVG( stars ) AS rating, which means the column from the DB will be titled rating. This doesn’t line up with the active record property, if you select as AVG( stars ) as stars does it work?

7

Thank you!

First: I changed it from rating to stars and doesn’t explained it in my threat. …

But your tip was perfect, the solution is:

[indent]


$crit->select = new CDbExpression('AVG(stars) as stars');

[/indent]That means, it is nessecary to build up the select statement via CDbExpression.

That could work for all these thinks, like MIN, MAX etc. - I will check it …

Thank you again.

[indent] [/indent]

8