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:
Bug or feature?
How can we solve this problem and save ActiveFinder logic & behavior?
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).
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.
