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?

Golub

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);
    }
}

Demonology

Ethan. Thank you SO MUCH! It worked :) Why symfony didn't generate this method automatically? I believe it's a really common task to delete something from your many to many table. – Golub

Yeah, I've always wondered why symfony doesn't generate remove methods too (since it does generate addMethods). Glad it worked! – Demonology

What if there are 1,000,000 records in the many-to-many table related to this user and I need to delete only one? Doctrine will load all the records? – Ennis

@AlexeyKosov Doctrine will load the collection in this case, hydrating each & every one of those objects. In this case DQL and/or QueryBuilder are the way to go. – Karolinekaroly

On this particular case a phan error Call to method removeElement on non-class type was driving me nuts and your comment and $groups is initialized to an empty ArrayCollection in the User class's constructor helped me solve it: my property wasn't initialized in the constructor. Thank you so much! – Solita

This will only remove 1 record, what if I want to remove all records of USERGROUP related to particular USER ?? – Kettie

Answer 2

In addition to @Ethan's answer, one-way remove is not working. For such manyToMany relationship, you have to call the remove methods from both entities, for example,

$user = $em->findOneById($userId);
$group = $em->findOneById($groupId);

$user->removeGroup($group);
$group->removeUser($user);

$em->persist($user);
$em->flush();

Werra

Is your solution equivalent of Delete from Table where col1=<val> and col2=<val>? Does it make query in Joined Table? – Skepticism

It seems to be like that – Werra