Yii Framework Forum: How To Do This Query In Yii? - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

How To Do This Query In Yii? Rate Topic: -----

#1 User is offline   kitune 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 121
  • Joined: 27-February 11

Posted 27 November 2012 - 04:18 AM

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
)

0

#2 User is offline   redguy 

  • Master Member
  • PipPipPipPip
  • Yii
  • Group: Members
  • Posts: 776
  • Joined: 02-July 10
  • Location:Central Poland

Posted 27 November 2012 - 04:49 AM

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

#3 User is offline   Keith 

  • Elite Member
  • Yii
  • Group: Moderators
  • Posts: 1,502
  • Joined: 04-March 10
  • Location:UK

Posted 27 November 2012 - 04:58 AM

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

0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users