define a scope with static relation

Hello,

maybe someone has the the same Problem and could help me. I want to define a scope in a model with declared relations. One relation is static.

class User extends CActiveRecord{...


public function relations(){

		return array(

			...

			'videos' => array(self::HAS_MANY, 'Video', 'creator_id'),

			'averageRating' => array(self::STAT, 'Video', 'creator_id', 

							'condition'	=> 'status_id = 3', 

							'select' 	=> 'AVG(rating)'),

			...

		)...

If I create now a scope with the ‘videos’ relation, it works! But if I will create a scope with the static one, this failed. Here my code that works:

'scopeTest' => 	array(	'select'	=> 't.id',

			'with'		=> array('videos'=>array('alias'	=>'v', 

								 'select' 	=> 'status_id, rating', 	

								 'joinType'	=>'INNER JOIN')),

			'condition' 	=> 'v.status_id = 3 AND v.rating >= 9,

		)

This code works, but I couldn’t generate a avarage rating - maybe its not supported. And the other relation didn’t work:

'scopeTest' => 	array(	'select'	=> 't.id',

			'with'		=> array('averageRating'=>array('alias'	=> 'r', 

									'on'	=> 't.id = r.creator_id', 

									'joinType'=>'INNER JOIN')),

			'condition' => "r.rating >= 9,

		)

My purpose is to create a list with users but purge the users, which have videos whose rating is to small. There are further eliminations, which should handled with scopes. But at first I have to find a solution for this problem.

Thx for all help.

Greetings…

Hi badi,

As far as I know, we can not use STAT relations for filtering and sorting.

Please take a look at this wiki.

http://www.yiiframework.com/wiki/319/searching-and-sorting-by-count-of-related-items-in-cgridview/

I’m not using scopes in the wiki, but I hope it will give you some hint on the problem.

Hi badi,

Check out these topics, hope they’ll be helpful:

http://www.yiiframew…+tat#entry41608

http://www.yiiframew…+tat#entry98346

http://www.yiiframew…tat#entry101002

Hi softark, Hi yugene,

at first, thanks for your replies. I’ve checked out your links and tried all proposals, but without success. Your wiki, softark, is very nice and helpful written. I tried to realize your hint generating a public variable for my ‘average_rating’, but I couldn’t take it in a scope.

For me its important, to have scopes for many use cases in my business processes.

By the way: Do I have well-understood, that a scope make my Model-Array smaler and smaler?

Eg:

User::model()->findAll() ==> 500 Users found,

User::model()->firstScope() ==> 100 Users found,

User::model()->firstScope()->secondScope() ==> 50 Users found

If my understanding is correct, how can I realize this SQL in a scope:

 SELECT u.* from tbl_user u where (u.id NOT IN (SELECT u2.id FROM tbl_user u2 

	INNER JOIN

		(SELECT v.user_id, AVG(rating) FROM 	

			(SELECT * FROM tbl_video WHERE status_id = 3) AS v 

 	   WHERE (rating >= 9) GROUP BY user_id) AS avg 

	ON (u2.id = avg.user_id)))

 

For every user a calculated average rating, with a minimal rating of 9. I think the subquery in the INNER JOIN is the problem, how can I form this in a scope?

Thanks in advance and greetz

badi

Ok, i found a solution in a scope that works. Maybe its the SQL-translation in scope-syntax, maybe its flawed, but it works

class User extends CActiveRecord{

...

public function scopes(){

    return array(

      ...

 	'biggerThenAverageRating'	=>	array(

 		'condition' => 't.id NOT IN (SELECT u.id FROM tbl_user u INNER JOIN

                                  (SELECT v.user_id, AVG(rating) FROM 

                           			(SELECT * FROM tbl_video WHERE status_id = :rated) AS v 

                                  WHERE (rating >= :ratinglimit) GROUP BY user_id) AS avg 

                       			ON (u.id = avg.user_id))',

          'params' => array(

   			':rated' => Video::TYPE_RATED,

   			':ratinglimit' => Test::RATINGLIMIT,

          ),  												

      ),

      ...

    )

}

}

Thanks again for all your help,

greetz badi