Active Record and Alias

Here the situation is quite complex (for me atleast).

my database engine is MyIsam

I have a table herb

another table synonyms

another table herb_images

another table lanugages

each synonyms has exactly one herb (HAS_ONE)

each synonyms has exactly one language (HAS_ONE)

The relations are defined as follows

In protected->models->Synonym.php

        public function defaultScope(){

            return array(



                'order'=>'language.language_name, synonym',



        public function relations()


		// NOTE: you may need to adjust the relation name and the related

		// class name for the relations automatically generated below.

		return array(

                    'language'=>array(self::HAS_ONE,'Language','','on'=>'Syn.language_id = language.language_id'),



Now the problem arises, when I call the line

$synonyms = Synonym::model()->with('language')->findAllByAttributes(array('herb_id'=>$herbId));

It gives me an error

CDbCommand failed to execute the SQL statement: SQLSTATE[42S22]: Column not found: 1054 Unknown column 't.herb_id' in 'where clause'

WHERE the generated sql is

Error in querying SQL: SELECT `Syn`.`synonym_id` AS `t0_c0`,

`Syn`.`language_id` AS `t0_c1`, `Syn`.`synonym` AS `t0_c2`, `Syn`.`herb_id`

AS `t0_c3`, `Syn`.`last_update` AS `t0_c4`, `language`.`language_id` AS

`t1_c0`, `language`.`language_name` AS `t1_c1`, `language`.`last_update` AS

`t1_c2`, `language`.`direction` AS `t1_c3` FROM `synonym` `Syn`  LEFT OUTER

JOIN `language` `language` ON (Syn.language_id = language.language_id) 

WHERE (`t`.`herb_id`=:yp0) ORDER BY language.language_name, synonym


And I am stuck here,

Also if I call the Synonym without the language restriction ( as it is in default scope) in the action function of view class,

$synonyms = Synonym::model()->findAllByAttributes(array('herb_id'=>$herbId));

the problem changes to

Invalid argument supplied for foreach()



        throw new CException(Yii::t('yii','Map data must be an array or an object implementing Traversable.'));

00259:     }


00261:     /**

00262:      * Merges two arrays into one recursively.

00263:      * @param array array to be merged to

00264:      * @param array array to be merged from

00265:      * @return array the merged array (the original arrays are not changed.)

00266:      * @see mergeWith

00267:      */

00268:     public static function mergeArray($a,$<img src='' class='bbc_emoticon' alt='B)' />

00269:     {

00270: foreach($b as $k=>$v)

00271:         {

00272:             if(is_integer($k))

00273:                 $a[]=$v;

00274:             else if(is_array($v) && isset($a[$k]) && is_array($a[$k]))

00275:                 $a[$k]=self::mergeArray($a[$k],$v);

00276:             else

00277:                 $a[$k]=$v;

00278:         }

00279:         return $a;

00280:     }


00282:     /**

can anyone help me out of this?

Has anyone considered giving a solution/answer for this problem?

You did NOT define herb relationship in your relations method.

@jamesmoey: He doesnt have to.

@Rehan: It seems like a bug to me. Yii does not get the proper alias. May u can do a workaround like this:

$synonyms = Synonym::model()->with('language')->findAllByAttributes(array('syn.herb_id'=>$herbId));

Thanks for the help

Actually both of ideas didn’t worked.

However, I found 2 solutions

1)If we have defined alias for a single table, then we have to do it for each table we are using… quite a lot of work, but cannot do anything else.

2)Secondly, my relations uses the on clause to define which table belongs to which using which column, quite a lengthy procedure, but I need it that way, therefore I have to add those relations in defaultscope so that everything works how it is intended to be.

If you just want to get all the synonyms for a particular herb, you can add a relation in your Herb model:

'synonyms' => array( self::HAS_MANY, 'Synonym', 'herb_id' ),

Which would allow you to grab all the synonyms for an herb by doing:

$aSyns = $arHerb->synonyms;