CakePHP model saving to wrong table

Question

In my User model I have the following relationship:

public $hasAndBelongsToMany = array(
        'User'=>array(
            'className'              => 'User',
            'joinTable'              => 'friends',
            'with'                   => 'Friend',
            'foreignKey'             => 'user_id',
            'associationForeignKey'  => 'friend_id'
        )
);

Which links users to users as friends. However when I do something with a single user like change the password or edit the user details (unrelated to the friends), it will start doing stuff in the friends table such as delete all existing records and then add in empty rows of data...

Have I set the relationship up wrong? Should the naming be different, so that when I'm dealing with the $this->User it is not touching friends?

Edit: Since posting this question I have changed the user_id and friend_id to user1_id and user2_id to prevent Cake doing any automagic with the fields by assuming that they are primary keys or anything as explained below by nIcO. But the same problem still happens!

Answer 1

I would strongly suggest doing this with a real Friends model. Set it up like this:

User hasMany FriendList
User hasMany FriendOf (alias of FriendList)

FriendList belongsTo User
FriendList belongsTo Friend (alias of User)

Example code:

Table definitions (skeleton):

users:
  id
  name

friend_lists:
  id
  user_id
  friend_id

Model Definitions:

class User extends AppModel {
  var $hasMany = array(
    'FriendList',
    'FriendOf' => array(
      'className' => 'FriendList',
      'foreignKey' => 'friend_id'
    )
  );
}

class FriendList extends AppModel {
  var $belongsTo = array(
    'User',
    'Friend' => array(
      'className' => 'User',
      'foreignKey' => 'friend_id'
     )
  );
}

Answer 2

Your join table is called friends and uses a model called Friend. And by looking at the HABTM relation declaration, it seems that the friends table contains a field called user_id and a field called friend_id.

Having a friend_id field in a friends table is like having a user_id field in a users table, which you would probably not do. And I guess that Cake doesn't like it because friend_id is seen by Cake as a foreign key pointing to another model called Friend.

Anyway, dealing with HABTM between the same table is not easy with HABTM relations (see HABTM with self requires 2x the rows in join table?) and you should probably use a join model instead (see http://book.cakephp.org/2.0/en/models/associations-linking-models-together.html#hasmany-through-the-join-model)

Answer 3

Don't create a Model that has the same alias/name as one used in the hasAndBelongsToMany relationship. This has caused me nothing but headaches with CakePHP.

For example;

I have a model called "Company" that HABTM "User" records. So there is a table called "companyies_users" in my database.

You might be tempted to do something like this in your Company model.

var $hasAndBelongsToMany = array(
    'User'=>array('with'=>'CompanyUser')
);

And then create a "CompanyUser" model, because you want to store extra data in the association.

Don't do this. I don't know why, but Cake is unpredictable of when it will auto-create a model named "CompanyUser" and when it will actually load the the PHP file you created.

It's best in your model that defines the HABTM to use the default association, and then create a Model of a different name from the table, and use that to process the records. This ensures that CakePHP will also uses an auto-generated model and work correctly.

So in my example, I create a model called "Members" that sets the "useTable" varaible to "companies_users".

Now I know when I use the "Company" model CakePHP will handle the HABTM correctly, and if I need to access those connection records, then I use the "Members" model.

The risk of using the "with" option is that you never know when CakePHP will fail, and auto-generate the mode. In which case, none of your callbacks or behaviors will happen.

EDIT:

To more directly answer the question.

Modify the User.php Model file so that the HABTM uses more of the defaults.

 public $hasAndBelongsToMany = array('Friend');

Ensure, you do not have a UserFriend.php Model file defined for the join table. If you need to modify the join table, use a model with a different alias pointing to that table in the database.

Answer 4

I guess your problem is caused by declaring the association incorrectly. Try this:

public $hasAndBelongsToMany = array(
        'Friend'=>array(
            'className'              => 'Friend',
            'joinTable'              => 'friends',
            'foreignKey'             => 'user_id',
            'associationForeignKey'  => 'friend_id'
        )
);

Answer 5

There are a few ways to implement a friendship association, because there are two directions in a friendship. You can say that user A is friends with user B, but also user B is friend with user A.

When a person creates a friendship in the database. You'll have to create two records in the join table to represent this friendship in both directions (assuming you want a dual direction).

That covers the concepts of direction.

You may be experiencing problems with records disappearing, because CakePHP by default will erase all join data rows for a record when it's updated. Take a look at the description for the 'unique' option on a HABTM.

unique: If true (default value) cake will first delete existing relationship records in the foreign keys table before inserting new ones, when updating a record. So existing associations need to be passed again when updating.

That means that you always have to include the HABTM data when updating the record. Otherwise those records will be erased, and updated with what was passed.

Now the most basic friendship setup you can create would involve two tables.

CREATE TABLE `users` (
   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
   `name` varchar(45) NOT NULL,
   PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `users_users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL,
  `friend_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `user` (`user_id`),
  KEY `friend` (`friend_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

The first table 'users' will hold our user data, and the second 'users_users' is the HABTM join table. Note the name, that it's users_users because it's joining itself.

You will still need to create two models in CakePHP.

The first is the User.php which will describe a user in the system. We'll be using mostly the defaults in CakePHP to get this to work. So the HABTM is just one line 'Friend'.

class User extends AppModel
{
    var $name = 'User';
    var $hasAndBelongsToMany = array(
          'Friend'
    );
}

Now, CakePHP will complain that the table users_friends is missing. We need to create a Friend model, but point it at the users table instead.

class Friend extends AppModel
{
    var $name = 'Friend';
    var $useTable = 'users';
}

Now CakePHP will use users_users table as the join. The Friend model now describes a user who is a friend.

It's important to understand that you will have to create the friends in both directions. When you save data for user A says he/she is friends with user B, then you will also have to save user B and say he/she is friends with user A. Otherwise, only one user will have the friendship defined.

There is another answer here that defines the dual direction of the friendship using aliases, but I recommend not implementing that as it adds extra SQL query work for CakePHP that might not be needed by your application. When you read records for User A, then you only need to know who their friends are, but no the other way around.

You could create a Behavior that ensures the friendship go both ways when user records are updated. That would be more efficient.

NOTE: My code example is for CakePHP 1.3 which is what I'm currently using, but it should work in 2.x just fine.

Answer 6

According to Cakephp HABTM Documentation,

HABTM data is treated like a complete set, each time a new data association is added the complete set of associated rows in database is dropped and created again so you will always need to pass the whole data set for saving.

From 2.1, with

You can set unique setting to keepExisting circumvent losing extra data during the save operation. I.e,

public $hasAndBelongsToMany = array(
        'User' => array(
                ....
                'unique' => 'keepExisting',
                ....
        ),
);

http://book.cakephp.org/2.0/en/models/associations-linking-models-together.html#hasmany-through