How To Do This Query In Yii?

Hi, I know that there is a findAllBySql() function but, is there a solution using yii like relations, scopes, etc I don’t know…

why cant you outer join with both tables simultaneously and process data based on MyField value?




SELECT CASE WHEN A.MyField = 1 THEN B.Field ELSE C.Field END ...

FROM A

  LEFT OUTER JOIN B ON A.Id = B.Id

  LEFT OUTER JOIN C ON A.Id = C.Id

WHERE (A.MyField = 1 AND B.Id IS NOT NULL) OR (A.MyField = 2 AND C.Id IS NOT NULL)



or something like this?

anyway - standard querying in ActiveRecord with criteria does not involve unions, so I guess that you have to rewrite your query or use findAllBySql…

You might be able to do it with two separate relations if you add a condition to the ON clause:




    'rel1'=>array(self::JOIN_TYPE, 'Model1', 'fk', 'on'=>'MyField = 1'),

    'rel2'=>array(self::JOIN_TYPE, 'Model2', 'fk', 'on'=>'MyField = 2'),



Then join both when querying:




    ParentModel::model()->with('rel1', 'rel2')->findAll();



You will have to check the value of MyField each time to determine whether rel1 or rel2 should be accessed for each record. I suppose most of the work could be encapsulated in methods of your parent class though, something like:




    public function getRel()

    {

        return $this->MyField == 1 ? $this->rel1 : $this->rel2;

    }