php - Yii active record relation limit to one record -

i using php yii framework's active records model relation between 2 tables. join involves column , literal, , match 2+ rows must limited ever return 1 row.

i'm using yii version 1.1.13, , mysql 5.1.something.

my problem isn't sql, how configure yii model classes work in cases. can classes work (simple eager loading) not (never lazy loading).

first describe database. goal. include examples of code i've tried , why failed.

sorry length, complex , examples necessary.

the database:

table sites columns:     id int     name varchar     type varchar rows:     id  name     type     --  -------  -----     1   site   foo     2   site b   bar     3   site c   bar  table field_options columns:     id int     field varchar     option_value varchar     option_label varchar rows:     id  field        option_value   option_label     --  -----------  -------------  -------------     1   sites.type   foo            foo style site     2   sites.type   bar            bar-like site     3   sites.type   bar            bar site 

so sites has informal reference field_options where:

1. field_options.field = 'sites.type' and
2. field_options.option_value = sites.type

the goal:

the goal sites relevant field_options.option_label go type value. if there happens more 1 matching row, pick 1 (any one, doesn't matter which).

using sql easy, can 2 ways:

1. i can join using subquery:

    select         sites.id,         f1.option_label type_label     sites     left join field_options f1 on f1.id = (         select id field_options                     field_options.field = 'sites.type'             , field_options.option_value = sites.type         limit 1     ) 

2. or can use subquery column reference in select clause:

    select         sites.id,         (             select id field_options                             field_options.field = 'sites.type'                 , field_options.option_value = sites.type             limit 1         ) type_label     sites 

either way works great. so how model in yii??

what i've tried far:

1. use "on" array key in relation

i can simple eager lookup work code:

class sites extends cactiverecord {     ...     public function relations()     {         return array(             'type_option' => array(                 self::belongs_to,                 'fieldoptions', // that's class field_options                 '', // no normal foreign key                 'on' => "type_option.id = (select id field_options field = 'sites.type' , option_value = t.type limit 1)",             ),         );     } } 

this works when load set of sites objects , force eager load type_label, e.g. sites::model()->with('type_label')->findbypk(1).

it not work if type_label lazy-loaded.

$site = sites::model()->findbypk(1); $label = $site->type_option->option_label; // error: column t.type doesn't exist 

2. force eager loading always

building on #1 above, tried forcing yii always eager loading, never lazy loading:

class sites extends cactiverecord {     public function relations()     {         ....     }     public function defaultscope()     {         return array(             'with' => array( 'type_option' ),         );     } } 

now works when load sites, it's no because there other models (not pictured here) have relations point sites, , result in errors:

$site = sites::model()->findbypk(1); $label = $site->type_option->option_label; // works  $other = othermodel::model()->with('site_relation')->findbypk(1); // error: column t.type doesn't exist, because 't' refers othermodel 

3. make reference base table somehow relative

if there way refer base table, other "t", guaranteed point correct alias, work, e.g.

'on' => "type_option.id = (select id field_options field = 'sites.type' , option_value = %%base_table%%.type limit 1)", 

where %%base_table%% refers correct alias table sites. know of no such token.

4. add true virtual database column

this way best, if convince yii table has column, should loaded every other column, except sql subquery -- awesome. again, don't see way mess column list, it's done automatically.

so, after that... have ideas?

edit mar 21/15: spent long time investigating possibility of subclassing parts of yii job done. no luck.

i tried creating new type of relation based on belongs_to (class cbelongstorelation), see if somehow add in context sensitivity react differently depending on whether being lazy-loaded or not. yii isn't built way. there no place can hook in code during query buiding inside relation object. , there no way can tell base class is, relation objects have no link parent model.

all of code assembles these queries active records , relations locked in separate set of classes (cactivefinder, cjoinquery, etc.) cannot extended or replaced without replacing entire ar system pretty much. that's out.

i tried see if can create "fake" database column entries subquery. answer: no. figured out how add additional columns yii's automatically generated schema data. but,
a) there's no way define column in such way can derived value, yii assumes it's column name in way many places that; and
b) there doesn't appear way avoid having try insert/update columns on save.

so looking yii (1.x) not have way make happen.

limited solution provided @eggyal in comments: @eggyal has suggestion meet needs. suggests creating mysql view table add columns each label, using subquery value. allow editing, view have tied separate yii class, downside everywhere in code need aware of whether i'm loading record reading (must use view's class) or read/write (must use base table's class, not have columns). said, workable solution particular case, maybe solution -- although not answer question written, i'm not going put in answer.

ok, after lot of attempts, have found solution. @eggyal making me think database views.

as quick recap, goal was:

• link 1 yii model (cactiverecord) using relation()
• the table join complex , match more 1 row
• the relation must never join more 1 row (i.e. limit 1)

i got work by:

• creating view field_options base table, using sql group by eliminate duplicate rows
• creating separate yii model (cactiverecord class) view
• using new model/view relation(), not original table

even there wrinkles (maybe yii bug?) had work around.

here details:

the sql view:

create view field_options_distinct     select         field,         option_value,         option_label             field_options     group         field,         option_value ; 

this view contains columns care about, , ever 1 row per field/option_value pair.

the yii model class:

class fieldoptionsdistinct extends cactiverecord {     public function tablename()     {         return 'field_options_distinct'; // view     }      /*         found needed following override yii's default table data.         view doesn't have primary key, , confused yii's ar finding system         , resulted in php "invalid foreach()" error.          code below works around diving yii table metadata object         , manually setting primary key column list.     */     private $bmetadataset = false;     public function getmetadata()     {         $ometadata = parent::getmetadata();         if (!$this->bmetadataset) {             $ometadata->tableschema->primarykey = array( 'field', 'option_value' );             $this->bmetadataset = true;         }         return $ometadata;     } } 

the yii relation():

class sites extends cactiverecord {     // ...      public function relations()     {         return (             'type_option' => array(                 self::belongs_to,                 'fieldoptionsdistinct',                 array(                     'type' => 'option_value',                 ),                 'on' => "type_option.field = 'sites.type'",             ),         );     } } 

and trick. easy, right?!?