need help with Relational Active Record that not return value like i expected

I need help with relational Active Record that not work like i thought.

I have 3 table, that i want to fetch using single model -person model- :

table_person :

  • index (pk)

  • name

  • address

  • city_code

  • country_code

table_city :

  • index (pk)

  • city_code

  • city_name

table_country :

  • index (pk)

  • country_code

  • country_name

like you see i not put any constrain all table. In "table_person" model i put this relation without defining PK on relation because its using diffrent PK than index in table :




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(

			'city'=>array(self::HAS_ONE,'table_city','[intentionaly empty]','on'=>'city_code=city.city_code'),

			'country'=>array(self::HAS_ONE,'table_country','[intentionaly empty]','on'=>'country_code=country.country_code'),

		);

	}



i expected when i pass person to variable $PERSON. And call value




$PERSON->city->city_name



or




$PERSON->country->country_name



should return "city_name" or "country_name" with matching city_code or country_code, but it not return like it.

Instead it always return first row from "city_table" or "country_table".

I have change self::HAS_ONE to self::BELONGS_TO and ‘on’=>‘city_code=city.city_code’ to ‘condition’=>‘city_code=city.city_code’, but with no avail.

Can someone help me fix it?

Thank You.

You should specify the FK in the relationship (not PK). In your case apparently city_code and country_code.

/Tommy

Where i should specify the FK ?

Is in database (mysql) using constraint or in "public function relations()"?

Thank You

Assuming city_code, country_code are FK’s




'city'=>array(self::HAS_ONE,'table_city','city_code','on'=>'...'),

'country'=>array(self::HAS_ONE,'table_country','country_code','on'=>'...'),



/Tommy

Well its still failed, because add FK’s (ex. city_code) in relation, Active Record will check primary key against FK

in my case will produce SQL command :




table_city.city_code=table_person.index



Above fix that you suggested should be ok IF the "table_city" is using "city_code" as primary key, in my case "city_code" is not the primary key on "table_city" the primary key in the table was "index". thus my relation always failed.

I have try change "on" parameters using this (with empty FK parameter, if FK not empty it will failed)




'on'=>'city_code=77'



and its run as intended. For example if "table_city" contain :

index city_code city_name

2 10 Jakarta

5 77 Bali

call this : $PERSON->city->city_name and it will return "Bali".

The problem is, i cant change “77” on “‘on’=>‘city_code=77’” to city_code column on primary table (table_person).

using below code all is failed (all error is "unknown column name in…"):




'on'=>'city_code=table_person.city_code'






'on'=>'table_city.city_code=table_person.city_code'






'on'=>'table_city.city_code=t.city_code' // "t" in yii-guide-1.1.0 is refer to primary table by default



Thank You

I didn’t read your first post very well, did I. :-[

So far I haven’t explored this type of problem, yet I know of commercial products using this type of constraintless linking. Must admit I don’t understand all implications of the HAS_ONE relationship type. Will try it out later. Perhaps somebody else can help you out now.

Is querying table_city and table_country an option? Then you should be able do declare BELONGS_TO relationships in corresponding models. In Yii 1.1, I don’t think it’s neccessary to declare the FK constraint in DB (it was in early 1.0.x).

/Tommy

I think this should work

(use the relationship name instead of table name)




'on'=>'city.city_code=t.city_code'



I tested something similar but I used eager loading.

Assuming your query is




$PERSON = Person::model()->find(...)



if no success you may want to try




$PERSON = Person::model()->with('city')->find(...)



Hope this helps

/Tommy

Thank you now its work like i expected. Its like u suggested, i change the relation.

Strangely, using lazy loading will produce unknown column error.

But again as you suggested, i change using eager loading and its work!!

Yes it’s helps. Thank You again :)