[munemune]

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.

[tri]

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

/Tommy

[munemune]

tri, on 10 February 2010 - 02:08 AM, said:

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

[tri]

munemune, on 10 February 2010 - 04:24 AM, said:

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

[munemune]

tri, on 10 February 2010 - 04:42 AM, said:

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

[tri]

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

[tri]

munemune, on 10 February 2010 - 08:27 AM, said:

...

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

...

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

[munemune]

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

tri, on 10 February 2010 - 12:34 PM, said:

I think this should work
(use the relationship name instead of table name)

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

Strangely, using lazy loading will produce unknown column error.

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

tri, on 10 February 2010 - 12:34 PM, said:

if no success you may want to try

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

tri, on 10 February 2010 - 12:34 PM, said:

Hope this helps

Yes it's helps. Thank You again