[kitune]

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

Quote

Questión:

Suppose I have table A with a field that can be either 1 or 2...

How do I select such that for each row in table A, if the field is 1, join the select with table B and if the field is 2, join the select with table C?

Answer:

(
SELECT MyField1, MyField2 FROM A
INNER JOIN B ON A.Id = B.Id
AND A.MyField = 1
)
UNION
(
SELECT MyField1, MyField2 FROM A
INNER JOIN C ON A.Id = C.Id
AND A.MyField = 2
)

[redguy]

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

[Keith]

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;
    }