Conditional Relations

My question is somewhat similar to this one, but not quite:

I have an external database (external as in not one I directly maintain, but I can query it) I interface with that has several tables that have very similar data structures and (mostly) differ only by the "type" of widget we are talking about:

external tables:




Now, I have set up a table to help consolidate this information into one table for referential integrity purposes (so when I need to refer to a particular widget, I instead reference the record on my side so I don’t have to store two columns every time I want to reference a widget from a different table). For each record only 1 widget ID is set and the rest are null. They also have appropriate foreign keys set.

create table widget_collection(

id int not null primary key auto_increment,

widget1_id int null,

widget2_id int null,

widget3_id int null


I have relations set up in this table like so. Remember, only one can be "active" at a time:

class WidgetCollection extends CActiveRecord {


public function relations()


        return array(

            'widget1' => array(self::BELONGS_TO, 'Widget1', 'widget1_id'),

            'widget2' => array(self::BELONGS_TO, 'Widget3', 'widget2_id'),

            'widget3' => array(self::BELONGS_TO, 'Widget3', 'widget3_id'),




I have a helper function in the WidgetCollection class that lets me lazy load the correct widget relation once the base record is retrieved (but notably, not eager loading) by doing this:

public function getWidget()


        if ($this->widget1_id!= null) {

            return $this->widget1;

        } elseif ($this->widget2_id!= null) {

            return $this->widget2;

        } elseif ($this->widget3_id!= null) {

            return $this->widget3;


        return null;


Now I have run into a situation where I would like to be able to eager load a bunch of records along with the appropriate widget relation, without knowing beforehand which particular widgets I will be retrieving. They could very easily be different widgets. I would need to be able to have Yii store the correct information into the Widget1, Widget2, or Widget3 class as appropriate. Is this possible using my current setup? Can it be done if I change the data structure?

Using my example above, the ideal solution would be for me to be able to eager load the records, and then I just retrieve the correct widget by doing $widgetCollection->widget, and that will return Widget1, Widget2, or Widget3 as appropriate.

I guess I should clarify:

I’m aware I could probably do something like this:

$coll = WidgetCollection::model()->with(array('widget1','widget2','widget3'))->findAll();

foreach($coll as $widgetColl) {

   $externalWidget = $widgetColl->getWidget();


I’m just wondering if there is a more “elegant” solution I guess, that does not require me to query on all possible widget tables.

Technically this is called ‘polymorphic relation’. There are several ways of implementing it, the simplect is to use two fields: widget_name and widget_id (so the relation join condition would be 't.widget_name = “Widget1” and t.widget_id =")

Anyway, I’m afraid you have to join all the tables because you can never know if some of them is not used. Try to create plain SQL query for this and you’ll see.

I was afraid of that, and just typing out the question helped me to see that. Thank you for confirming it.

lol it’s not so scary as it sounds :)

The only real "issue" I suppose is if I wanted to do any filtering on the query, it is relatively complicated:

condition = ‘widget1.column = :whatever OR widget2.column = :whatever OR widget3.column = :whatever’

But if I wrap it in a named scope it isn’t too bad. And the performance still seems pretty good for my purposes, so it works out.