Yii Framework Forum: AR Relation Foreign Key - Yii Framework Forum

Jump to content

  • (2 Pages)
  • +
  • 1
  • 2
  • You cannot start a new topic
  • This topic is locked

AR Relation Foreign Key Rate Topic: ***** 3 Votes

#1 User is offline   Johny Joe 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 21
  • Joined: 26-July 09

Posted 03 September 2009 - 02:33 AM

I have 2 tables.

TUser (id, uid, name)
TPosts (id, tu_uid, posts)

uid and ta_uid are the connection key between those two tables. (A user can have multiple posts)

How to define the relation in TUser model ?

thx
Everyday, is holiday.
0

#2 User is offline   Dave 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 187
  • Joined: 09-October 08

Posted 03 September 2009 - 02:56 AM

class TUser extends CActiveRecord
{
    public function relations()
    {
        return array(
            'posts'=>array(self::HAS_MANY, 'TPost', 'tu_uid'),
        );
    }
}


More information you will find in the guide. http://www.yiiframew...de/database.arr
0

#3 User is offline   Johny Joe 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 21
  • Joined: 26-July 09

Posted 03 September 2009 - 03:08 AM

View PostDave, on 03 September 2009 - 02:56 AM, said:

class TUser extends CActiveRecord
{
    public function relations()
    {
        return array(
            'posts'=>array(self::HAS_MANY, 'TPost', 'tu_uid'),
        );
    }
}


More information you will find in the guide. http://www.yiiframew...de/database.arr


I mean, connecting uid and ta_uid uid is a student-code (example: A102)

The relation above, connecting id and ta_uid right?
Everyday, is holiday.
0

#4 User is offline   Dave 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 187
  • Joined: 09-October 08

Posted 03 September 2009 - 03:25 AM

So uid is not the primary key of the user? Normally you do not specify the column of the first part of the relation because the pk is always chosen.

In that case you need to do it on your own I guess.

public function getPosts()
{
  TPost::model()->findAll(array('ta_uid = :id', array(':id' => $this->id)))
}

1

#5 User is offline   Johny Joe 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 21
  • Joined: 26-July 09

Posted 03 September 2009 - 03:44 AM

View PostDave, on 03 September 2009 - 03:25 AM, said:

So uid is not the primary key of the user? Normally you do not specify the column of the first part of the relation because the pk is always chosen.

In that case you need to do it on your own I guess.

public function getPosts()
{
  TPost::model()->findAll(array('ta_uid = :id', array(':id' => $this->id)))
}


Yes uid is not the primary (but its unique)
Is there another alternatif? like doing INNER JOIN via controller?

Anyway, Thanks!
Everyday, is holiday.
0

#6 User is offline   pestaa 

  • past Yii dev member
  • PipPipPipPip
  • Yii
  • Group: Members
  • Posts: 705
  • Joined: 07-May 09
  • Location:Hungary

Posted 03 September 2009 - 03:52 AM

Have you tried defining 'on' clause in the relation? That may override the pk-restriction.
1

#7 User is offline   Johny Joe 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 21
  • Joined: 26-July 09

Posted 03 September 2009 - 03:59 AM

View Postpestaa, on 03 September 2009 - 03:52 AM, said:

Have you tried defining 'on' clause in the relation? That may override the pk-restriction.


I haven't tried that, any example how to use it? coz none in the demo-blog-pdf.....
Everyday, is holiday.
0

#8 User is offline   pestaa 

  • past Yii dev member
  • PipPipPipPip
  • Yii
  • Group: Members
  • Posts: 705
  • Joined: 07-May 09
  • Location:Hungary

Posted 03 September 2009 - 04:03 AM

In TUser relations:

'posts'=>array(self::HAS_MANY, 'TPosts', 'tu_uid', 'on'=>'tu_uid=uid'),

0

#9 User is offline   Johny Joe 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 21
  • Joined: 26-July 09

Posted 03 September 2009 - 04:25 AM

View Postpestaa, on 03 September 2009 - 04:03 AM, said:

In TUser relations:

'posts'=>array(self::HAS_MANY, 'TPosts', 'tu_uid', 'on'=>'tu_uid=uid'),



It didn't work (error).... I check the generated query,

INNER JOIN [dbo].[TPosts] t1 ON (t1.[tu_uid]=[dbo].[TUser].[id]) AND (tu_uid=uid)


If I change the query (manually) into
INNER JOIN [dbo].[TPosts] t1 ON (t1.[tu_uid]=[dbo].[TUser].[uid])

then execute it it works...

some how, the connection with the Primary Key can't be erased....
Everyday, is holiday.
0

#10 User is offline   pestaa 

  • past Yii dev member
  • PipPipPipPip
  • Yii
  • Group: Members
  • Posts: 705
  • Joined: 07-May 09
  • Location:Hungary

Posted 03 September 2009 - 05:11 AM

That was a bad idea from me. Active record is designed to handle relations with foreign keys pointing to primary keys.

With the current implementation, you cannot make reference to non-primary fields. I suggest you to change tu_uid to tu_id, so that your primary field values are used to define this relation. Your table size will also be smaller this way.
1

#11 User is offline   Dave 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 187
  • Joined: 09-October 08

Posted 03 September 2009 - 05:56 AM

Besides, I do not understand why you have two columns that uniquely identify a data set. You can just skip 'u_id' because this column does not provide any more information according to a relation, because you already have a primary key that does exactly that.
0

#12 User is offline   Johny Joe 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 21
  • Joined: 26-July 09

Posted 03 September 2009 - 06:51 AM

View Postpestaa, on 03 September 2009 - 05:11 AM, said:

That was a bad idea from me. Active record is designed to handle relations with foreign keys pointing to primary keys.


oh, thx for the information.

View PostDave, on 03 September 2009 - 05:56 AM, said:

Besides, I do not understand why you have two columns that uniquely identify a data set. You can just skip 'u_id' because this column does not provide any more information according to a relation, because you already have a primary key that does exactly that.


it's my client application database tables, I'm not authorized do modifications.
Everyday, is holiday.
0

#13 User is offline   pestaa 

  • past Yii dev member
  • PipPipPipPip
  • Yii
  • Group: Members
  • Posts: 705
  • Joined: 07-May 09
  • Location:Hungary

Posted 03 September 2009 - 07:50 AM

You may want to override the query generator, so that customized ON clause won't be appended but replaced.
0

#14 User is offline   Johny Joe 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 21
  • Joined: 26-July 09

Posted 03 September 2009 - 04:48 PM

View Postpestaa, on 03 September 2009 - 07:50 AM, said:

You may want to override the query generator, so that customized ON clause won't be appended but replaced.


well, that's a bit harder for me. I think I'll skip the AR part and use DAO (for now).
Everyday, is holiday.
0

#15 User is offline   qiang 

  • Yii Project Lead
  • Yii
  • Group: Yii Dev Team
  • Posts: 5,900
  • Joined: 04-October 08
  • Location:DC, USA

Posted 03 September 2009 - 07:30 PM

*
POPULAR

Try this in relations:
'posts'=>array(self::HAS_MANY, 'TPosts', '', 'on'=>'tu_uid=uid'),


Basically, you rely on the 'on' option to join the tables rather than the FK constraints.
6

#16 User is offline   Johny Joe 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 21
  • Joined: 26-July 09

Posted 03 September 2009 - 10:27 PM

View Postqiang, on 03 September 2009 - 07:30 PM, said:

Try this in relations:
'posts'=>array(self::HAS_MANY, 'TPosts', '', 'on'=>'tu_uid=uid'),


Basically, you rely on the 'on' option to join the tables rather than the FK constraints.


All right, here it goes:
//file: models/TUser.php
...
'TPosts'=>array(self::HAS_MANY, 'TPosts', '', 'on'=>'tu_uid=uid', 'joinType'=>'INNER JOIN', 'alias'=>'TPosts')
...


//file: models/TPosts.php
...
'TUser'=>array(self::BELONGS_TO, 'TUser', '', 'on'=>'uid=tu_uid', 'joinType'=>'INNER JOIN', 'alias'=>'TUser')
...


And when I want to see all posts by name 'Sidney', I just use:
//file: controllers/postsController.php
...
$pRows = TPosts::model()->with('TUser')->findAll(
	"TUser.name = :name",
	array(
		':name' => "Sidney"
	)
);

foreach($pRows as $row){
	echo $row->TUser->uid;	// shows her uid
	echo $row->posts;	// shows her posts
}
...


An alternative, I can do this too:
//file: controllers/userController.php
...
//get the user first
$u = TUser::model()->find(
	"TUser.name = :name",
	array(
		':name' => "Sidney"
	)
);

//get all her posts	
$pRows = $u->TPosts;
		
foreach($pRows as $row){
	echo $row->TUser->uid;	// shows her uid
	echo $row->posts;	// shows her posts
}
...


Thanks again Qiang.
Everyday, is holiday.
2

#17 User is offline   totha 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 9
  • Joined: 15-September 10

Posted 12 October 2010 - 05:50 AM

jj, qiang - thank you for this thread so much!
0

#18 User is offline   damncabbage 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 5
  • Joined: 20-March 10
  • Location:Down the side of the couch.

Posted 04 November 2010 - 03:35 PM

Qiang, JJ: Thanks for saving my butt. This was very useful; if I have time I'll see if I can add it to the Cookbook.
0

#19 User is offline   Brokentwig 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 5
  • Joined: 19-April 11

Posted 23 April 2011 - 05:38 PM

Thanks guys, been trying to understand the hidden logic in these relations. That cleared it up. This info should really be in the guide.
0

#20 User is offline   Karl Zilles 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 48
  • Joined: 07-June 11
  • Location:Los Angeles, United States

Posted 07 June 2011 - 06:21 PM

Thanks, this was very useful.
0

Share this topic:


  • (2 Pages)
  • +
  • 1
  • 2
  • You cannot start a new topic
  • This topic is locked

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