How do I remove a self referencing n:n relation in Doctrine?

Question

I'd like to represent e friend relationship between two users in Doctrine. I am able to create the relationship, but I don't know what's the best way to delete it.

I have the following schema:

User:
  columns:
    name: string(255)
  relations:
    Friends:
      class: User
      local: user1
      foreign: user2
      refClass: FriendReference
      equal: true

FriendReference:
    columns:
        user1:
            type: integer
            primary: true
        user2:
            type: integer
            primary: true

Here's how I create the relationsship:

$user1 = new User();
$user2 = new User();
$user1->Friends[] = $user2;

This works perfectly.

mysql> select * from friend_reference;
+-------+-------+
| user1 | user2 |
+-------+-------+
|     4 |     5 |
+-------+-------+
1 row in set (0.01 sec)

Now what's the best way to remove the relationship if I have the two primary keys 4 and 5 of the relationship to delete? I could

1. Fetch the object with the ID 4 and Iterate through $user->Friends and then use unlink to delete this relationship.

2. Write the following query

   $query = Doctrine_Query::create()
      ->delete('FriendReference')
      ->where('(user1=4 AND user2=5) OR (user2=4 AND user1=5)')
      ->execute();
   

I think both options neither elegant nor performant.

Answer 1

I'd go with second option. It's not as nice, as the first one, but iterating over all friends to delete one does not seem to be such a good idea.

Answer 2

I'd probably do something like this:

class User extends Doctrine_Record {

   public function removeFriend (User $friend) {
      $this->getTable()->execute('remove.friend', 
         array('user1' => this->id, 'user2' => $friend->id));
   }
}

class UserTable extends Doctrine_Table {
   public function setUp () {
      $this->addNamedQuery('remove.friend',
         Doctrine_Query::create()
            ->delete('FriendReference')
            ->where('(user1=:user1 AND user2=:user2) 
                     OR (user2=:user1 AND user1=:user2)'
   }
}

//Some other place:
$user->removeFriend($noLongerMrNiceGuy);

You can read about the recommended way to remove n-m relationship links here. Excerpt from the docs:

The right way to delete links between many-to-many associated records is by using the DQL DELETE statement. Convenient and recommended way of using DQL DELETE is through the Query API.

// test.php
$q = Doctrine_Query::create()
    ->delete('GroupUser')
    ->addWhere('user_id = ?', 5)
    ->whereIn('group_id', array(1, 2));

$deleted = $q->execute();

Another way to unlink the relationships between related objects is through the Doctrine_Record::unlink method. However, you should avoid using this method unless you already have the parent model, since it involves querying the database first.