Yii Framework Forum: need help with Relational Active Record that not return value like i expected - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

need help with Relational Active Record that not return value like i expected Rate Topic: -----

#1 User is offline   munemune 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 22
  • Joined: 07-November 09

Posted 09 February 2010 - 11:39 PM

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.
0

#2 User is offline   tri 

  • Elite Member
  • Yii
  • Group: Moderators
  • Posts: 1,651
  • Joined: 20-November 08
  • Location:Stockholm, Sweden

Posted 10 February 2010 - 02:08 AM

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

/Tommy
Don't forget to read The Definitive Guide to Yii (en) (sv) | The class reference has the details
0

#3 User is offline   munemune 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 22
  • Joined: 07-November 09

Posted 10 February 2010 - 04:24 AM

View Posttri, 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
0

#4 User is offline   tri 

  • Elite Member
  • Yii
  • Group: Moderators
  • Posts: 1,651
  • Joined: 20-November 08
  • Location:Stockholm, Sweden

Posted 10 February 2010 - 04:42 AM

View Postmunemune, 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
Don't forget to read The Definitive Guide to Yii (en) (sv) | The class reference has the details
0

#5 User is offline   munemune 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 22
  • Joined: 07-November 09

Posted 10 February 2010 - 08:27 AM

View Posttri, 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
0

#6 User is offline   tri 

  • Elite Member
  • Yii
  • Group: Moderators
  • Posts: 1,651
  • Joined: 20-November 08
  • Location:Stockholm, Sweden

Posted 10 February 2010 - 10:44 AM

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
Don't forget to read The Definitive Guide to Yii (en) (sv) | The class reference has the details
0

#7 User is offline   tri 

  • Elite Member
  • Yii
  • Group: Moderators
  • Posts: 1,651
  • Joined: 20-November 08
  • Location:Stockholm, Sweden

Posted 10 February 2010 - 12:34 PM

View Postmunemune, 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
Don't forget to read The Definitive Guide to Yii (en) (sv) | The class reference has the details
0

#8 User is offline   munemune 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 22
  • Joined: 07-November 09

Posted 11 February 2010 - 04:22 AM

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

View Posttri, 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!!

View Posttri, on 10 February 2010 - 12:34 PM, said:


if no success you may want to try
$PERSON = Person::model()->with('city')->find(...)



View Posttri, on 10 February 2010 - 12:34 PM, said:

Hope this helps


Yes it's helps. Thank You again :)
0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users