Yii Framework Forum: Joins Order When Finding Record With Has_Many Relation And 'join' In Criteria - Yii Framework Forum

Jump to content

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

Joins Order When Finding Record With Has_Many Relation And 'join' In Criteria bug or feature Rate Topic: ***** 1 Votes

#1 User is offline   Bethrezen 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 76
  • Joined: 24-December 08
  • Location:Tambov, Russia

Posted 14 September 2012 - 02:19 AM

Suppose we have model Products and it HAS_MANY relation of models Options.
If we are finding Products model with it's Options and want to use join in criteria - the order of joins in result query can be wrong.
For example:

// note: Not real-life, but handcrafted example, but shows the problem on git master @ September, the 13, 2012
$criteria = new CDbCriteria;
$criteria->join = "INNER JOIN Options opt ON (opt.productId = t.id)";
$criteria->condition = "categoryId=1";

$model = Products::model()->with('options')->find($criteria);



The result SQL query for finding Products model will include JOIN for related models Options(as we used with), but this joins will be added after criteria->join.
As result we can't join in criteria by some condition that uses JOINs of related models - MySQL doesn't allow to do this causing error in query.

The reason is order of joins creation in CActiveFinder.php.
I've made a dirty hack to CJoinElement find function, but it can break some other existing code by its side effects.

public function find($criteria=null)
	{
		if($this->_parent===null) // root element
		{
//hack start
			$joins = $criteria->join; 
			$criteria->join = "";
//hack end			
			$query=new CJoinQuery($this,$criteria);
			$this->_finder->baseLimited=($criteria->offset>=0 || $criteria->limit>=0);
 
 
 
			$this->buildQuery($query);
//hack start
			$query->joins[]=$joins;
//hack end
			$this->_finder->baseLimited=false;
			$this->runQuery($query);
		}
		else if(!$this->_joined && !empty($this->_parent->records)) // not joined before
		{
			$query=new CJoinQuery($this->_parent);
			$this->_joined=true;
			$query->join($this);
			$this->buildQuery($query);
			$this->_parent->runQuery($query);
		}
 
		foreach($this->children as $child) // find recursively
			$child->find();
 
		foreach($this->stats as $stat)
			$stat->query();
	}



Questions:
1. Bug or feature?
2. How can we solve this problem and save ActiveFinder logic & behavior?
My site and Blog

dotPlant CMS based on Yii Framework
0

#2 User is offline   samdark 

  • Having fun
  • Yii
  • Group: Yii Dev Team
  • Posts: 3,738
  • Joined: 17-January 09
  • Location:Russia

Posted 14 September 2012 - 05:36 AM

Why can't you use LEFT JOIN in this case?
Yii 1.1 Application Development Cookbook

Enjoying Yii? Star us at github: 1.1 and 2.0.
0

#3 User is offline   Bethrezen 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 76
  • Joined: 24-December 08
  • Location:Tambov, Russia

Posted 14 September 2012 - 05:54 AM

View Postsamdark, on 14 September 2012 - 05:36 AM, said:

Why can't you use LEFT JOIN in this case?


That's just an example or showing incorrect order of joins.
In real life: I can't use LEFT JOIN, because I need INNER JOIN for some kind of dynamic filtering related models (i.e. cars with automatic transmission type.. that's crazy structure there).
My site and Blog

dotPlant CMS based on Yii Framework
0

#4 User is offline   samdark 

  • Having fun
  • Yii
  • Group: Yii Dev Team
  • Posts: 3,738
  • Joined: 17-January 09
  • Location:Russia

Posted 14 September 2012 - 12:29 PM

Hmm. If it's a bug then in order to preserve current behavior we need unit tests. Without unit tests I don't think we can solve it.
Yii 1.1 Application Development Cookbook

Enjoying Yii? Star us at github: 1.1 and 2.0.
0

#5 User is offline   Bethrezen 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 76
  • Joined: 24-December 08
  • Location:Tambov, Russia

Posted 15 September 2012 - 01:36 AM

That's why i've decided to post here but not to issues on github.
My site and Blog

dotPlant CMS based on Yii Framework
0

#6 User is offline   samdark 

  • Having fun
  • Yii
  • Group: Yii Dev Team
  • Posts: 3,738
  • Joined: 17-January 09
  • Location:Russia

Posted 16 September 2012 - 04:17 AM

Since currently you're the one who understand problem better it will be great if you'll work on unit tests.
Yii 1.1 Application Development Cookbook

Enjoying Yii? Star us at github: 1.1 and 2.0.
0

#7 User is offline   Bethrezen 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 76
  • Joined: 24-December 08
  • Location:Tambov, Russia

Posted 16 September 2012 - 04:22 AM

View Postsamdark, on 16 September 2012 - 04:17 AM, said:

Since currently you're the one who understand problem better it will be great if you'll work on unit tests.


On my projects this kind of 'hack' didn't break anything.
I'll try unit testing. But before merging it to master it'll be better to ask community to test this on their real projects.

Anyway, I think my way of undefining join's in criteria is not good design. That's just a little hack for changing order of joins. The 'true-way' of solving this should be done in design and main architecture of ActiveFinder. But I don't well understand the core design of this components. So, maybe there is a better way to solve this problem.
My site and Blog

dotPlant CMS based on Yii Framework
0

#8 User is offline   anup 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 5
  • Joined: 18-January 12
  • Location:INDIA

Posted 26 October 2012 - 03:12 AM

I am not sure but in this can't we add relation array
Like your should be "option_rel"=>array("self::HAS_MANY","Options","productId","JOIN_TYPE"=>"Inner Join","condition"=>"Blah blah");
Instead of $criteria->join = "INNER JOIN Options opt ON (opt.productId = t.id)";

Please let me know if I am wrong
0

#9 User is offline   Bethrezen 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 76
  • Joined: 24-December 08
  • Location:Tambov, Russia

Posted 26 October 2012 - 03:30 AM

View Postanup, on 26 October 2012 - 03:12 AM, said:

I am not sure but in this can't we add relation array
Like your should be "option_rel"=>array("self::HAS_MANY","Options","productId","JOIN_TYPE"=>"Inner Join","condition"=>"Blah blah");
Instead of $criteria->join = "INNER JOIN Options opt ON (opt.productId = t.id)";

Please let me know if I am wrong


I need to add condition dynamically based on user input and filter rules, that were set in control panel(admin).
I know the way of setting conditions in relations, but that's not suitable in current situation.
My site and Blog

dotPlant CMS based on Yii Framework
0

Share this topic:


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

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