Deleting record in many-to-many table

Question

I'm following the security chapter of the Symfony 2 book.

There's is an example with a table USERS and GROUPS. There is a many-to-many relationship between USERS and GROUPS, which creates in the database a table called USERGROUPS.

What I want is to delete a record from USERGROUPS, for example:

DELETE from USERGROUPS WHERE user_id = 1 and group_id = 1 

I don't know how to do this since I don't have an USERGROUPS.php table file.

Using DQL, for example, I want to be able to do this:

$em = $this->getDoctrine()->getEntityManager();
$query = $em->createQuery(
    'DELETE FROM AcmeStoreBundle:UserGroups ug WHERE ug.user_id = :user 
    and ug.group_id = :group'
)->setParameter(array('user' => $userid, 'group' => $groupid));

I hope you get the idea.

Then, how do I remove from this table?

Answer 1

Doctrine thinks about the data as objects, rather than as table rows. So, in Doctrine terms, there are Group objects (which hold the Group's users, among other things) and there are User objects (each one of which has a property storing the Groups that the user is in). But there are no UserGroup objects. The idea of Doctrine (and any ORM system) is to let the developer forget about these intermediate tables that the database might need but that aren't necessary in terms of the program's object model.

So what you want to do is load up the relevant User object, remove the group from it's $groups property, and persist the modified User object. (Or vice-versa, i.e. load up the relevant Group object and remove the User from it.) DQL might be able to handle this, but I think it's easier to do it without DQL as DQL's DELETE statement is for deleting whole objects, not modifying their properties.

Try:

$user = $em->find('User', $userId);
$user->removeGroup($groupId); //make sure the removeGroup method is defined in your User model. 
$em->persist($user);
$em->flush(); //only call this after you've made all your data modifications

Note: if you don't have a removeGroup() method in your User model (I think Symfony can generate one for you, but I could be wrong), the method could look as follows.

//In User.php, and assuming the User's groups are stored in $this->groups, 
//and $groups is initialized to an empty ArrayCollection in the User class's constructor
//(which Symfony should do by default).

class User
{
    //all your other methods

    public function removeGroup($group)
    {
        //optionally add a check here to see that $group exists before removing it.
        return $this->groups->removeElement($group);
    }
}