Using relations and conditions

Try to use ‘on’ parameter, not ‘condition’ when defining a relation with a condition, especially in HAS_ONE relation.

Consider definition of the person that has bank accounts and sometimes has a main account:




public function relations()

{

	return array(

		'accounts'=>array(self::HAS_MANY, 'Account', 'userId'),


		'mainAccount_good'=>array(self::HAS_ONE, 'Account', 'userId',

			'on'=>'mainAccount_good.main=1'),


		'mainAccount_wrong'=>array(self::HAS_ONE, 'Account', 'userId',

			'condition'=>'mainAccount_wrong.main=1'),


		'mainAccount_works_too'=>array(self::HAS_ONE, 'Account', 'userId',

			'condition'=>'mainAccount_works_too.main=1 or mainAccount_works_too.main IS NULL'),

		);

}




$people = Person::model()->with('mainAccount_good')->findAll();

Will work properly, because it will return people having or not having a mainAccount set because the condition main=1 is put in ON clause in the query.

People who don’t have a main account (an account with a field “main” set to “1”) will have null as the value of the mainAccount_good relation.


$people = Person::model()->with('mainAccount_wrong')->findAll();

Will not work properly, because the condition is put in WHERE clause at the end of the whole query and the whole query is filtered.

So it will return people with their accounts but only people who have "main" set to "1".

So people who don’t have a main account will be filtered out.

What if we don’t join tables and want to filter rows from just 1 table?

Consider loading a record by


$person = Person::model()->findByPK(1);

Lazy loading an relation with a condition defined by ‘on’ clause works too!

Yii properly converts ON clause to WHERE clause in the query.


$account = $person->mainAccount_good;

Of course


$account = $person->mainAccount_wrong;

will work properly this time because the condition is put in WHERE clause.

I have not tested using ‘together’=false parameter but i think above examples will be useful for some people… even those who don’t have a bank account :P

Best regards.

I completely agree. Often you see, that most people put all conditions into the WHERE clause, including those for the JOINed table. It might work in most cases, but it’s not good practice. Things get even worse for INNER JOIN.

So: Good to state that. Maybe more developers pay attention.

Fixed in 1.1.3

"- Chg #1323: Conditions declared in scopes of the related AR classes will be put in the ON clause of the JOIN statement (Qiang)"

Look also at issue 1323

Thanks to Developers for their work!

:D Thx for the tip… I sometimes made this mistake

veris you are [size="5"]Great!!![/size]

Thanks a lot for your help. I lost about 2 hours trying to figure this.

Yessss, thank you very much, your tips is very nice !

can somebody explain me this line?

$people = Person::model()->with(‘mainAccount_good’)->findAll();

what does with() do…?

actually i have same type of line

$dataProvider=new CActiveDataProvider(Question::model()->recent()->with(‘user’)->with(‘answers’));

and i dont know the meaning oof this…

thanks in advance…

It’s a relational query, which is described in the guide here.

I am assuming each call for relational object queries the MySQL server right?

//Post has comments;

//Comment belongs to Post;

$model = Post::model()->findByPk(1);//queries once

$comments = $model->comments;// queries second time

[/size]

Yes it does. But you can avoid that. Please read the section on query options and query performance in the guide.

Hi everybody,

Could anybody give a clew about using scopes while defining a relation? Make this sense?

I have 2 model: first one (promotions) has 2 attributes "date_from" and "date_to" using them and I want to define 3 scopes (past(), current() and next() promotions) because they could be usefull.

In the other model (user) I would like to have 3 relations which relate each ‘user’ record with its past() promotions, current() promotions and next() promotions using scopes defined in promotions.

Currently I have defined 3 different relations using ‘on’ (like Veris said) but without using scopes yet. Could I integrate all the stuff? You know, define scopes in one model and use them in other model while defining relations?

Thank you in advance.

How to bound primary key to on condition in yii relations?

Context, real situation:

One Source can relate to several different Modifications (MANY_MANY), each modification relate to some Product (BELONGS_TO). If several Products has one Source, it means that the Products are same - that’s criteria. (I can’t just merge same products, because it may turn out that they are not the same, but if I merge them - I can’t split them back).

So, when I need to find all orders related to some product, I actually want to find all orders with same products, not only with current product.

Relation looks like this:


'orderedProducts'=>array(self::HAS_MANY,'OrderProduct','','on'=>('modification_id IN (

select DISTINCT ms2.modification_id FROM products p1

LEFT JOIN products_modifications pm ON pm.product_id = p1.product_id

LEFT JOIN modifications_sources ms ON ms.modification_id = pm.modification_id

LEFT JOIN modifications_sources ms2 ON ms2.source_id = ms.source_id

where p1.product_id='.$this->primaryKey.'

)')),


'orders'=>array(self::HAS_MANY,'Order',array('order_id'=>'order_id'),'through'=>'orderedProducts'),

$this->primaryKey is not working, It’s here just to show where I need to bound primary key.

Any suggestions how to bound primary key there?


Added question to stackoverflow: ◊ http://stackoverflow.com/questions/16441222/how-to-bound-primary-key-to-custom-on-condition-in-yii-relations

still looking for answer!

Thanx Veris.

Our Example`

public function relations()

{

	[indent]return array([/indent]


		[indent][indent]'user' => array(self::BELONGS_TO, 'User', 'user_id'),[/indent][/indent]   


	[indent])[/indent]


}


    


    public function defaultScope()


    {


            [indent]return array([/indent]


                [indent][indent]'alias'=>'nonget',[/indent][/indent]


                [indent][indent]'condition'=>'nonget.user_id = '.Yii::app()->user->id,[/indent][/indent]


            [indent]);[/indent]


    }

Best Regards www.iSystems.am Official Russia & Armenia