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.
// 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
$joins = $criteria->join;
$criteria->join = "";
$this->_finder->baseLimited=($criteria->offset>=0 || $criteria->limit>=0);
else if(!$this->_joined && !empty($this->_parent->records)) // not joined before
foreach($this->children as $child) // find recursively
foreach($this->stats as $stat)
Bug or feature?
How can we solve this problem and save ActiveFinder logic & behavior?
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.