Accessing relational data

Hello there,

I having a little issue try to access a country dictionary table. There are two tables: Agent and Dictionary; the first table storages a country_id field thats relate to Dictionary with this code block in relations() method:

'dictionaryCountry' => array( self::BELONGS_TO, 'Dictionary', '',

	'on' => '`dictionaryCountry`.`type` = \'' . DICTIONARY_TYPE_COUNTRY . '\' And code = t.country_id' ),

I have successfull created the CRUD, accessing the Dictionary table on CDetailView views with:


		'name' => 'country',

		'value' => ($model->dictionaryCountry->text) ? $model->dictionaryCountry->text: "??",

		'sortable' => 'true',

                'header' => 'País',


But at the moment of accessing thru related tables, like $data->customer->dictionaryCountry->text ($data storages an Invoice CActiveDataProvider) it came with this CDbException:

SQL: SQLSTATE[42S22]: Column not found: 1054 Unknown column 't.country_id' in 'where clause'

As I see, the issued SQL command didn’t append the Agent table in the query.

Reviewing pieces of code, it seems necessary to add using with() method at the query (which I have properly added at search() method), but for this issue I don’t know where I have to add it…

Thanks for any clue,


try with:

'dictionaryCountry' => array( self::BELONGS_TO, 'Dictionary', 'country_id',

        'condition' => '`dictionaryCountry`.`type` = \'' . DICTIONARY_TYPE_COUNTRY . '\' ),

If you specify the third parameter Yii will authomatically generate the on, avoiding you alias problems.

If you sort on related table, you should use $criteria->toghether = true.

Hello zaccaria,

thanks for your answer.

Testing your approach I get: [sql]SQL: SQLSTATE[42000]: Syntax error or access violation: 1064[/sql]. The SQL command was:

SELECT `t`.`agent_id` AS `t0_c0`, `t`.`type` AS

`t0_c1`, `t`.`country_id` AS `t0_c2`, `t`.`taxid` AS `t0_c3`, `t`.`name` AS

`t0_c4`, `dictionaryCountry`.`type` AS `t1_c0`, `dictionaryCountry`.`code`

AS `t1_c1`, `dictionaryCountry`.`text` AS `t1_c2`,

`dictionaryCountry`.`extra1` AS `t1_c3`, `dictionaryCountry`.`extra2` AS

`t1_c4` FROM `Agent` `t`  LEFT OUTER JOIN `Dictionary` `dictionaryCountry`

ON ()  WHERE (`t`.`agent_id`='328') AND (`dictionaryCountry`.`type` =

'country' And code = t.country_id)

Notice the [sql]ON ()[/sql] empty clause…


That’s strange.

Try using innoDb, set the foreign key and generate the model with gii, it will create the relation correct as you need.

You hit into the real problem! I use a composite key onto Dictionary with a discriminant field (‘type’ where it saves a constant tablename). That’s why I couldn’t use Yii native relations.

Is there a way to add a with() in the relations() block? Or perhaps is better to writter a propper method to access country name?


Why don’t to make the foreign key on the field country_id and then add the check on type as a condition?

It should work even like that.

Anyway you can simply do without relations, just add a getter method:

public function getdictionaryCountry()


  return Dictionary::model()->find(...your condition here...)


The disvantage is that you have to put more attention for search and sort on the related fields.

I’m trying that approach, in relations():

'dictionaryCountry' => array( self::BELONGS_TO, 'Dictionary', 'country_id',

	'condition' => '`dictionaryCountry`.`type` = \'' . DICTIONARY_TYPE_COUNTRY . '\'' ),

But it issue an incorrect comparation in the SQL command:

[sql]SELECT t.agent_id AS t0_c0, t.type AS t0_c1,

t.country_id AS t0_c2, t.taxid AS t0_c3, AS t0_c4,

dictionaryCountry.type AS t1_c0, dictionaryCountry.code AS

t1_c1, dictionaryCountry.text AS t1_c2,

dictionaryCountry.extra1 AS t1_c3, dictionaryCountry.extra2 AS

t1_c4 FROM Agent t LEFT OUTER JOIN Dictionary dictionaryCountry

ON (t.country_id=dictionaryCountry.type) WHERE

(t.agent_id=‘185’) AND (dictionaryCountry.type = ‘country’)


Notice the ON condition (which Yii writes) is [font="Lucida Console"]t.country_id=dictionaryCountry.type)[/font] where it should issue as: [font="Lucida Console"]t.country_id=dictionaryCountry.code)[/font].

On Dictionary model, primaryKey() method I have tested a few combinations: returning array( ‘type’, ‘code’ ), only ‘code’ and deleting the method.

At database I replaced primary key from (type, code) to (code, type) onto Dictionary table, without luck…

Surely I missing something…


I think that Yii has problems with composite key, I always avoid it.

Usually I create a new field autoincrement as foreign key and rely on it for relations.

Yeap! I read (and write) a few comments about it… but I can’t change the DBA model.

Will continue to find a solution.

Really appreciate your help zaccaria!