Yii Framework Forum: Using relations and conditions - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

Using relations and conditions Rate Topic: ***** 6 Votes

#1 User is offline   veris 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 21
  • Joined: 03-July 09
  • Location:POLAND

Posted 01 July 2010 - 04:50 PM

*
POPULAR

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.
Zbigniew Gralewski
15

#2 User is offline   Mike 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 3,016
  • Joined: 06-October 08
  • Location:Upper Palatinate

Posted 02 July 2010 - 03:18 AM

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.
0

#3 User is offline   veris 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 21
  • Joined: 03-July 09
  • Location:POLAND

Posted 05 July 2010 - 02:25 AM

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!
Zbigniew Gralewski
0

#4 User is offline   mech7 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 220
  • Joined: 26-March 09

Posted 16 July 2010 - 05:44 AM

:D Thx for the tip... I sometimes made this mistake
0

#5 User is offline   gerhat 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 12
  • Joined: 04-May 09

Posted 15 November 2010 - 04:20 PM

veris you are Great!!!

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

#6 User is offline   nath-0 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 81
  • Joined: 12-October 12
  • Location:switzerland

Posted 05 November 2012 - 09:03 AM

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

#7 User is offline   nkhanna 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 12
  • Joined: 27-January 13

Posted 28 January 2013 - 02:15 AM

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..
0

#8 User is offline   Mike 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 3,016
  • Joined: 06-October 08
  • Location:Upper Palatinate

Posted 28 January 2013 - 03:15 AM

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

#9 User is offline   Firebreaker 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 3
  • Joined: 23-April 12
  • Location:Mongolia

Posted 06 March 2013 - 04:23 AM

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
0

#10 User is offline   Mike 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 3,016
  • Joined: 06-October 08
  • Location:Upper Palatinate

Posted 06 March 2013 - 04:39 AM

View PostFirebreaker, on 06 March 2013 - 04:23 AM, said:

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


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

#11 User is offline   Jose H. Milán 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 28
  • Joined: 21-July 11
  • Location:España

Posted 12 March 2013 - 10:35 AM

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.
0

#12 User is offline   MaxLord 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 9
  • Joined: 31-March 12

Posted 08 May 2013 - 10:04 AM

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...n-yii-relations
still looking for answer!

This post has been edited by MaxLord: 14 May 2013 - 09:56 AM

0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

2 User(s) are reading this topic
0 members, 2 guests, 0 anonymous users