ActiveRecord and table relation question

Hi,

i have the folloing relation: user <- user_relation -> user

user_relation is a n:m table with id as pk, fromid and toid. Is it possible to query the relations in this way?

relation_user::model()->with('touser')->findByAttributes->(…)

The problem is that my user_relation has a PK called "id" and this will be joined with the user table and not my toid column property.

Is there a way to handle this?

Thanks

Boris

How did you declare the relation rule in the user AR?

The query will not bring in 'id' column from user_relation.

in the user class i have the following rule

'relations' => array(self::MANY_MANY, 'User', 'user_relation(fromid, toid)'),

and in the user_relation

'user' => array(self::HAS_ONE, 'User', 'id')

so what is your problem??

this is my code:

$criteria = new CDbCriteria;

$criteria->limit = $limit;

$criteria->offset = $offset;

$relations = Relation::model()->with('friend')->findAllByAttributes(array('fromid'=>$id), $criteria);

and the call generates this:

SELECT user_relation.relationid AS t0_c0, …

FROM user_relation 

LEFT OUTER JOIN user t1 ON t1.id=user_relation.fromid

WHERE user_relation.fromid='1' LIMIT 4

these are my models:

user:

id (pk)

firstname

lastname

user_relation:

fromid

toid

the question is, why does it take fromid? Does it take automaticly the first column? Cant i define which local column in the table is responsible for the join?

I mean in the relation rule like:

'user' => array(self::HAS_ONE, 'User', 'remote'=>'id', 'local'=>'toid')

It is based on FK constraint which defines a pair. It is not arbitrary. I would suggest you change your table schema because it is not well designed (mainly the FK constraints).

i cant follow…

i have the relation user -> user_relation -> user

so user_relation has to FKs back to the user table (fromid, toid). How can the code know which FK to take if i do user_relation::model()->find(…)

I dont think, that this might be a wrong approach for a n:m. Can you give more hints?

Thanks

Boris

When you declare a relation rules in relations(), you only need to specify the FK, not PK. Since a FK is always associated with a PK, that's why Yii knows which PK is relating with.

What would be your solution in this case?

I want to define two relations in the user_relation, 1. fromuser, 2.touser.

I have defined two FKs in the database, but how can i define in the relations() that for the fromuser relation the column/FK fromid is responsible and for the touser the column/FK toid.

Thanks for your really quick replies…

Boris

I don't know why you want to define a user_relation model. The user_relation table is mainly used to decouple n:m self-relationship in user table. So all you need is just the user model. Then, if you want to find out a user's friends, you can declare in user model a relation like the following:



'friends'=>array(self::MANY_MANY,'Category','user_relation(fromid, toid)'),


Ok, now its getting hotter :)

I have actually this relation you mentioned. I have had a thread where i asked you if i can access the user_relation data if i would use user::model()->with('friends')…

You told me that the n:m table cant have any data, but what i need :frowning: So we agreed to try to query the user_relation directly… :wink:

Thanks

Boris

Yes, that's the limit of RAR because it can't model the relation table very well.