[Johny Joe]

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

[Dave]

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

[Johny Joe]

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?

[Dave]

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

[Johny Joe]

Dave, 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!

[pestaa]

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

[Johny Joe]

pestaa, 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.....

[pestaa]

In TUser relations:

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

[Johny Joe]

pestaa, 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....

[pestaa]

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.

[Dave]

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.

[Johny Joe]

pestaa, 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.

Dave, 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.

[pestaa]

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

[Johny Joe]

pestaa, 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).

[qiang]

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.

[Johny Joe]

qiang, 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.

[totha]

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

[damncabbage]

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.

[Brokentwig]

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

[Karl Zilles]

Thanks, this was very useful.