Accessing relational data

Yii 1.1 General Discussion
1

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:


	array(

		'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,

cbi

2

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.

3

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…

cbi

4

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.

5

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?

cbi

6

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.

7

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, 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 (t.country_id=dictionaryCountry.type) WHERE

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

[/sql]

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…

cbi

8

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.

9

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!

cbi