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

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:

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.

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

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 = t.id)";

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.