comparre 2 fields in db

Yii 1.1 General Discussion
1

hi i need to compare 2 fields in a table, do i need to do a join before i compare,

this doesn’t work

the table is

id || name || current_players || max_players|| state_code|| display




$groups=new CActiveDataProvider('Groups',array(

		'criteria'=>array(

		'condition'=>'display = 1 AND state=:state_code AND name !=:name OR display = 1 AND state=:all_code AND name !=:name OR current_players <= max_players',

		 'params'=>array(':state_code'=>Yii::app()->user->state,':all_code'=>'all',':name'=>'Lounge'),

		),  

		));

it does run the query but AND current_players <= max_players does nothing.

looking for an elegant solution.

mithereal

2

You can use with=> table_name or the property join of CDbCriteria

3

Ive been looking for documentation on hoe "with" works

ive tried using it like




	$groups=new CActiveDataProvider('Groups',array(

		'criteria'=>array(

		'with'=>array('Groups'=>'max_players'),

		'condition'=>'display = 1 AND state=:state_code AND name !=:name OR display = 1 AND state=:_code AND name !=:name OR current_players < max_players',

		 'params'=>array(':state_code'=>Yii::app()->user->state,':_code'=>'all',':name'=>'Lounge'),

		),  

		));

i keep getting te error

Relation "max_players" is not defined in active record class "Groups". or something similar.

do i need to create a self relation to the model?

4

You can create this self relation.

If you are doing this only once, maybe is more easy to use the join parameters:




 $groups=new CActiveDataProvider('Groups',array(

                'criteria'=>array(

                'join'=>'JOIN ....',

                'condition'=>'display = 1 AND state=:state_code AND name !=:name OR display = 1 AND state=:_code AND name !=:name OR current_players < max_players',

                 'params'=>array(':state_code'=>Yii::app()->user->state,':_code'=>'all',':name'=>'Lounge'),

                ),  

                ));
5

but how do i specfy the criteria so it knows the differences between the colums

in sql it bel something like




SELECT a.minplayers, a.maxplayers, b.minplayers, b.maxplayers

FROM groups a, goups b

LIMIT 0 , 30


then i could check the critera like this


'condition'=>'a.minplayers > b.maxplayers;