Ar, Relations, Condition And Where Clause

I have an application which i supports, which using subqueries. This causes a performance issue.

As solution i want to use LEFT JOIN:




SELECT t1.*

FROM test t1 

LEFT JOIN test t2

ON (t1.`group` = t2.`group` AND t1.version < t2.version)

WHERE t2.version IS NULL;



but there is a problem: if i define new relation like this




'latest' => array(self::HAS_MANY, 'ContentItem', 'id',

                'joinType' => 'LEFT JOIN',

                'alias' => 'r',

                'on' => 't.version < r.version',

                'condition' => 'r.version IS NULL'

            ),



condition will be added in ON clause, so this method won’t work. This condition need to be called from named scope. How can i do this?

For now i solved this problem with join property in CDbCriteria, but it seems to me a bit dirty




'latestVersionOnly' => array(

                'join'=>'LEFT JOIN content r ON r.id=t.id AND t.version < r.version',

                'condition' => 'r.version IS NULL',

            ),