How To Change `with` And `join` Priorities In Cdbcriteria?

Yii 1.1 General Discussion
1

I need to construct a complicated query running against a main table and several dependent tables. I’m using it like this:


$criteria = new CDbCriteria;

$criteria->with = array('product', 'shop', 'address', 'unit');

The complicated part of the query is coded as SQL statement added by means of:


$criteria->join = 'join (SELECT...

The problem araises from the fact that when Yii builds resulting query it uses my join first and then appends its own LEFT OUTER JOIN for each dependent table. This leads to a SQL syntax error due to the complexity of the custom statement passed though $criteria->join. When I change manually the order of joins in the automatically generated query, it runs smoothly. So the question is how can I ensure that Yii will change the order in which it assembles data from with and join properties? I need my custom join to be the latest join in the query, and automatically generated LEFT OUTER JOINs to go before it.

Thanks in advance.

2

if you want to write a join query you may like this…




            $criteria = new CDbCriteria;

            $criteria->select = 't.*, tu.* ';

            $criteria->join = ' LEFT JOIN `user_crew` AS `tu` ON t.id = tu.user_id //second join';

            $criteria->addCondition("display_name LIKE '%a%' and blocked_by='76'");

            $resultSet    =    Customer::model()->findAll($criteria);

            return $resultSet;
3

I already have a join. The problem is that it goes first in resulting generated query, but should go last (after another joins generated by with). As for addCondition, it does not help. The error is Unknown column ‘t.id’ in ‘on clause’, where the ‘on clause’ is from with. The query should be like this:


SELECT *

FROM `maintable` `t`

LEFT OUTER JOIN  `subtable1`  `subtable1` ON (  `t`.`id` =  `subtable1`.`id` )

LEFT OUTER JOIN  `subtable2`  `subtable2` ON (  `t`.`id` =  `subtable2`.`id` )

...

JOIN

  (SELECT ...) AS `x`

   ON (`t`.`id` = `x`.`id` AND `t`.`cost` = `x`.`best`),

  (SELECT ...) `s1`

WHERE `t`.`id` = `s1`.`id`

  AND `t`.`time` = `s1`.`last`

This query works. What Yii generates is:


SELECT *

FROM `maintable` `t`

JOIN

  (SELECT ...) AS `x`

   ON (`t`.`id` = `x`.`id` AND `t`.`cost` = `x`.`best`),

  (SELECT ...) `s1`

LEFT OUTER JOIN  `subtable1`  `subtable1` ON (  `t`.`id` =  `subtable1`.`id` )

LEFT OUTER JOIN  `subtable2`  `subtable2` ON (  `t`.`id` =  `subtable2`.`id` )

WHERE `t`.`id` = `s1`.`id`

  AND `t`.`time` = `s1`.`last`

...

This breaks.

4

Hi can you post the $creiteria query?

5

As old as it is, did you try to do it that way?

Example:

$criteria->with = array(‘candidato’, ‘agendaExame’, ‘infracaoResultadoExame’, ‘infracaoResultadoExame.infracao’);
$criteria->addCondition(‘infracao.tipo_infracao_id_tipo_infracao = 1’);