AR Relation Foreign Key
#1
Posted 03 September 2009 - 02:33 AM
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
#2
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
#3
Posted 03 September 2009 - 03:08 AM
Dave, 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?
#4
Posted 03 September 2009 - 03:25 AM
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))) }
#5
Posted 03 September 2009 - 03:44 AM
Dave, on 03 September 2009 - 03:25 AM, said:
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!
#6
Posted 03 September 2009 - 03:52 AM
#7
Posted 03 September 2009 - 03:59 AM
pestaa, on 03 September 2009 - 03:52 AM, said:
I haven't tried that, any example how to use it? coz none in the demo-blog-pdf.....
#8
Posted 03 September 2009 - 04:03 AM
'posts'=>array(self::HAS_MANY, 'TPosts', 'tu_uid', 'on'=>'tu_uid=uid'),
#9
Posted 03 September 2009 - 04:25 AM
pestaa, on 03 September 2009 - 04:03 AM, said:
'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....
#10
Posted 03 September 2009 - 05:11 AM
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.
#11
Posted 03 September 2009 - 05:56 AM
#12
Posted 03 September 2009 - 06:51 AM
pestaa, on 03 September 2009 - 05:11 AM, said:
oh, thx for the information.
Dave, on 03 September 2009 - 05:56 AM, said:
it's my client application database tables, I'm not authorized do modifications.
#13
Posted 03 September 2009 - 07:50 AM
#14
Posted 03 September 2009 - 04:48 PM
pestaa, on 03 September 2009 - 07:50 AM, said:
well, that's a bit harder for me. I think I'll skip the AR part and use DAO (for now).
#15
Posted 03 September 2009 - 07:30 PM
POPULAR
'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.
#16
Posted 03 September 2009 - 10:27 PM
qiang, on 03 September 2009 - 07:30 PM, said:
'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.
#18
Posted 04 November 2010 - 03:35 PM
#19
Posted 23 April 2011 - 05:38 PM