Joins Order When Finding Record With Has_Many Relation And 'join' In Criteria

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 =";

$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);





//hack start


//hack end




		else if(!$this->_joined && !empty($this->_parent->records)) // not joined before


			$query=new CJoinQuery($this->_parent);







		foreach($this->children as $child) // find recursively



		foreach($this->stats as $stat)




  1. Bug or feature?

  2. How can we solve this problem and save ActiveFinder logic & behavior?

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

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.

That’s why i’ve decided to post here but not to issues on github.

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.

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 =";

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.