views:

68

answers:

2

I have a table called user_relationship. which has two foreign keys refering back to the User table to map that they are friends.

CREATE TABLE `user_relationships` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `status` varchar(255) default 'pending',
  `time` datetime default NULL,
  `user_id` int(11) unsigned NOT NULL,
  `friend_id` int(11) unsigned NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `fk_user_relationships_users1` (`user_id`),
  KEY `fk_user_relationships_users2` (`friend_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

when i try to bake it naturally doesnt understand that the friend_id has to refer to User module. i wanna manual edit the code but o have some problem understanding what parts to edit in the following

var $belongsTo = array(
    'User' => array(
        'className' => 'User',
        'foreignKey' => 'user_id',
        'conditions' => '',
        'fields' => '',
        'order' => ''
    ),
    'Friend' => array(
        'className' => 'Friend',
        'foreignKey' => 'friend_id',
        'conditions' => '',
        'fields' => '',
        'order' => ''
    )
);

In this part of the code i wanna refer the friend_id to the User Table

    'Friend' => array(
        'className' => 'Friend',
        'foreignKey' => 'friend_id',
        'conditions' => '',
        'fields' => '',
        'order' => ''
    )

i tried doing this.. do i need to change anything else ?

    'Friend' => array(
        'className' => 'Friend',
        'foreignKey' => 'user_id',
        'conditions' => '',
        'fields' => '',
        'order' => ''
    )
+1  A: 

Your classname for 'Friend' is wrong. Since you have no table "friends" there is no model "Friend" and you have the refer to the class "User" since they share the same table.

'Friend' => array(
    'className' => 'User',
    'foreignKey' => 'friend_id',
    'conditions' => '',
    'fields' => '',
    'order' => ''
)
Tim
thanks a lot TIM. what does the 'Friend' => array() refer to ?
Harsha M V
It refers to your user-table and uses "friend_id" as the foreignKey.
Tim
thank you TIM got it :D
Harsha M V
+1  A: 

How about this?

class UserRelationship {

    var $belongsTo = array(
        'User' => array(
            'className' => 'User',
            'foreignKey' => 'user_id'
        ),
        'Friend' => array(
            'className' => 'User',
            'foreignKey' => 'friend_id'
        )
    );

}

class User {

    var $hasAndBelongsToMany = array(
        'Friend' => array(
            'className' => 'User',
            'joinTable' => 'user_relationships',
            'foreignKey' => 'user_id',
            'associationForeignKey' => 'friend_id',
            'with' => 'UserRelationship'
         )
    );

}

Associations can be seen from different points of view:

User        <-    Relationship    ->     User
 |                     |                  |
 hasMany           belongsTo              |
 Relationship  <-  User (user_id)        hasMany
 |                 User (friend_id)  ->  Relationship
 |                                        |
 HABTM                                   HABTM
 User       <---------------------->     User

Your "physical" layout, i.e. database schema, is User -> Relationship -> User. Your real desired relationship is a User -> User relationship though. Technically that translates into a User hasAndBelongsToMany User association (also called many-to-many). Since it uses three models, a many-to-many association can be broken down into:

  • User hasMany Relationship/Relationship belongsTo User
  • Relationship belongsTo User/User hasMany Relationship

You don't need the hasAndBelongsToMany association, but it's what you actually want. You don't really need the Relationship model at all, since it's just a helper model to connect the two Users, but if you do want to specify its associations as well, it has two belongsTo associations.

deceze
can u explain why do we need to use $hasAndBelongsToMany. cant i get it done with 2 hasMany ?
Harsha M V
http://book.cakephp.org/view/1046/Multiple-relations-to-the-same-model
Harsha M V
@Harsha Updated with an attempted explanation...
deceze
got it :D wow.. CakePHP is cooler than i thought :D initially i wanted to save two rows in the user_relationship table so that it will be easy to find() user_relationship(id, user_id, friend_id, status) status being pending accepted etc. with HABTM i dont need two entries or will i still need it ?
Harsha M V
@Harsha Not sure what you mean...?
deceze
in the relationship table u_r(1, 22, 43, 'pending) u_r(2, 43, 22, 'pending) for a one relationship
Harsha M V
@Harsha I see. You'll still have this problem if you want the relationship to be "two-sided" ("Friend" also has "User" as friend). You either need two associations (one through the `user_id`/`friend_id` fields, another through `friend_id`/`user_id`), two rows in the relationship table or some custom SQL query that fetches the HATBM record regardless of whether it's connected through `user_id`/`friend_id` or `friend_id`/`user_id`. All techniques have their pros and cons regarding speed and simplicity.
deceze
thanks a lot deceze. i will go with the HABTM method. looks neat :D
Harsha M V
one last q. how did u drop that diagram of model associations ? do u use any editor or program ?
Harsha M V
@Harsha ASCII art, baby! Lots of spaces, enters and deletes... Typed right here on the site. :D
deceze
whoa :D kudos :D
Harsha M V