AR Relation Foreign Key


(Blackjenova) #1

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


(Deventer) #2



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.yiiframework.com/doc/guide/database.arr


(Blackjenova) #3

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

The relation above, connecting id and ta_uid right?


(Deventer) #4

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)))

}




(Blackjenova) #5

Yes uid is not the primary (but its unique)

Is there another alternatif? like doing INNER JOIN via controller?

Anyway, Thanks!


(Pestaa) #6

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


(Blackjenova) #7

I haven’t tried that, any example how to use it? coz none in the demo-blog-pdf…


(Pestaa) #8

In TUser relations:




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




(Blackjenova) #9

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…


(Pestaa) #10

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.


(Deventer) #11

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.


(Blackjenova) #12

oh, thx for the information.

it’s my client application database tables, I’m not authorized do modifications.


(Pestaa) #13

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


(Blackjenova) #14

well, that’s a bit harder for me. I think I’ll skip the AR part and use DAO (for now).


(Qiang Xue) #15

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.


(Blackjenova) #16

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.


(Mk Totha) #17

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


(Damncabbage) #18

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.


(Mustardseedsystems) #19

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


(Zilles) #20

Thanks, this was very useful.