Doctrine batch delete join table records

Question

I have a normal many to many relationship between two entities : User and Object.

________        _________________        ____________
| User |        |   User_Object |        |  Object  |
|------|        |---------------|        |----------|
|  id  |        |     user_id   |        |    id    |
| .... |        |    object_id  |        |   ....   |
|______|        |_______________|        |__________|

I want to batch delete pretty big sets of Users (and the records associated with them in the User_Object table). Removing the entities one by one isn't fast enough for my needs (for > 1000 entities it takes a loooonnnng time).

//This method is far too slow for my needs
$qb = $this->doctrine->em->createQueryBuilder();
$qb->select('u')
    ->from('Entities\User', 'u')
    ->where("u.whatever= ?1")
    ->setParameter(1, $whatever);
$users = $qb->getQuery()->getResult();

foreach($users as $user)
{
    $this->doctrine->em->remove($user);
    //...

The doctrine docs say that the most efficient to bulk delete entities is DQL, which would give me something like :

$qb = $this->doctrine->em->createQuery('delete from Entities\User u where u.whatever = ?1');
$qb->setParameter(1, $whatever);
$numDeleted = $qb->execute(); //This will throw because of User_Object records

This will throw an exception because of the records in the User_Object join table (referential integrity exception).

So, my question is : how do I delete the records in the join table efficiently in a bulk delete scenario.

I would really like to avoid throwing raw SQL at it, the rest of my code uses entities everywhere and I would like to keep it that way if at all possible.

EDIT : The relationship is marked as such (I use yml) :

manyToMany:
  objects:
  targetEntity: Object
  inversedBy: users
  cascade: ["remove"]
  joinTable:
    name: User_Object
    joinColumns:
      user_id:
        referencedColumnName: id
    inverseJoinColumns:
      object_id:
        referencedColumnName: id

score 0 · Answer 1 · answered Nov 04 '13 at 16:42

0

You haven't setup the behavior of doctrine if you are deleting the user for the object. You have to says doctrine that if the user is delete then all related object must be deleted too.

/**
 * @ORM\ManyToMany(targetEntity="User", mappedBy="objects", cascade={"remove"})
 */
protected $users;


/**
 * @ORM\ManyToMany(targetEntity="Object", inversedBy="users", cascade={"persist", "remove"})
 * @ORM\JoinTable(name="user_object",
 * joinColumns={@ORM\JoinColumn(name="user_id", referencedColumnName="id")},
 * inverseJoinColumns={@ORM\JoinColumn(name="object_id", referencedColumnName="id")}
 * )
 */
protected $objects;

This is an example. I didn't tested it.

answered Nov 04 '13 at 16:42

tyko

96
4

The relationship is marked as cascade remove. I have edited my question with my yml definition. I think this is what makes it work with EntityManager (but not with DQL directly). Is that possible? In any case, even with cascade remove it doesn't work. I still get the same error as before. – Hugo Migneron Nov 04 '13 at 17:29
You defined the cascade remove on the user side but not the object side. The cascade option has to be defined on the inverse side because you are deleting user and not object. When you are using DQL to delete stuff in your DB, to keep deleting through the relation you can use onDelete option too. The onDelete option is use on the database side instead of cascade. Cascade is use on the memory side. – tyko Nov 05 '13 at 09:44

Doctrine batch delete join table records

1 Answers1