I recently needed to create a data model which included a friend-type system. Not a social network type thing but simply the ability to tag other users of a system. Sounds simple enough!

The application is written in CakePHP which has some fairly good ORM facilities but what I wanted wasn’t really simple to implement. The relationship I wanted to configure was a hasAndBelongsToMany relationship with a single table. In other words the join table referenced just one table. The join table looked like this:

CREATE TABLE IF NOT EXISTS `users_tags` (
  `user_id` INT(11) NOT NULL,
  `tag_id` INT(11) NOT NULL
);

And here is where things get a bit harder. There is no tags table which by convention the HABTM would usually join up to. What I really wanted was this:

CREATE TABLE IF NOT EXISTS `users_users` (
  `user_id` INT(11) NOT NULL,
  `user_id` INT(11) NOT NULL
);

Which obviously won’t work as you can’t have two columns named the same. Fortunately everything in CakePHP is configurable so setting the table up like this:

CREATE TABLE IF NOT EXISTS `users_users` (
  `user_id` INT(11) NOT NULL,
  `tag_id` INT(11) NOT NULL
);

Then configuring the User model with this relationship:

var $hasAndBelongsToMany = array(
    'Tag' => array(
      'className' => 'User',
      'join_table' => 'users_users',
      'foreignKey' => 'user_id',
      'associationForeignKey' => 'tag_id'
    )
  );

Enabled me to pull out all the tagged users for a particular user. This is where the fun started for me. I now needed the ability to tag players. I set up a list of users you could tag with a controller action like this /users/tag/$id. As I was logged in I already had the user_id bit and was passing the tag_id into the controller action. I expect to be able to do:

$this->User->UserUser->set('tag_id', $tag_id);
$this->User->UserUser->set('user_id', $user_id);
$this->User->UserUser->save();

But no matter what I tried the data wouldn’t save as one row. For some reason two rows were inserted, both with a blank user_id and the two ids supplied as tag_ids. I know that HABTM calls delete before saving but I didn’t see how it would be affecting the code as above. I tried for a while to get the CakePHP way of doing this but couldn’t work it out. In the end I just used a plain old query() to achieve what I wanted:

$this->User->query('INSERT IGNORE INTO `users_users` SET `user_id`='.$id.', `tag_id`='.$tag_id);

Whilst this does the job I would rather have worked out why it wasn’t working in the CakePHP way, so if any experts out there can shed some light please do!

This wasn’t the end of my woes tho. The application also uses the Auth component. The HABTM relationship seemed to be causing the Auth component some issues, when it logged in it was issuing a save() call which resulted in all my tags being wiped out. The solution to this was to unbind the model in the beforeFilter() method:

function beforeFilter() {
    
    $this->Session->start();
    
    $this->User->unbindModel(array('hasAndBelongsToMany' => array('Tag')));

    $this->Auth->autoRedirect = false;
    $this->Auth->loginAction = array('controller' => 'users', 'action' => 'login');
    $this->Auth->loginRedirect = array('controller' => 'games', 'action' => 'grid');
    $this->Auth->logoutRedirect = array('controller' => 'users', 'action' => 'login');
    $this->Auth->allow('display','login','logout');
    $this->Auth->authorize = 'controller';
    $this->Auth->userScope = array('User.active' => 1);

  }

After this the tagging relationship seems to work a treat. Hopefully this will post will prove useful to anyone trying to achieve a similar result.