Update many-to-many association doctrine2

Question

Is there any solution to do this automatically?

My two entities:

class User
{
    /* *
    * @ManyToMany(targetEntity="Product", inversedBy="users")
    * @JoinTable(name="user_product",
    *  joinColumns={@JoinColumn(name="user_id", referencedColumnName="idUser")},
    * inverseJoinColumns={@JoinColumn(name="product_id", referencedColumnName="idProduct")}
    * 
    * )
    */
protected $products;
}

class Product {
    /**
    * @ManyToMany(targetEntity="User", mappedBy="products")
    */
protected $users;
}

User entity exists with two Products already associated ids (1, 2):

$user = $entityManager->find('User', 1);

This array came from view with new Products data to be inserted, deleted or if already in list do nothing:

$array = array(1, 3, 4);

In this case:

1 = Already in association with User (do nothing)
2 = not in array and should be deleted
3 = should be inserted
4 = should be inserted

How to do this in doctrine2? Is there a merge function that do it automatically or shoud I do it manually?

Answer 1

Consider the following code

$user = $entityManager->find('User', 1);
$products = array();

foreach(array(1, 3, 4) as $product_id) {
    $products[$product_id] = $entityManager->getReference('MyBundle\Entity\Product', $product_id);
}

$user->setProducts($products);    
$entityManager->persist($user);
$entityManager->flush();

And setProducts defined as

function setProducts($products) {
  $this->products = new ArrayCollection($products);
}

In this case doctrine will delete all the user's product associations and then insert each product association passed in from the view.

I tested this on my system where a visit entity is associated to many visit_tag entities. Note that doctrine deletes all visit_tag associations for a given visit object in profiler screenshot below and then creates each one.

In order to have doctrine only delete/insert associations as needed, you have to manually merge the existing $user->products ArrayCollection instead of overwriting it like above. And you can do this efficiently using indexed associations via the indexBy annotation, which lets you search/add/remove associations by a unique key (i.e. product id) in constant time.

class User
{
   /**
    * @ManyToMany(targetEntity="Product", inversedBy="users", indexBy="id")
    * @JoinTable(name="user_product",
    *  joinColumns={@JoinColumn(name="user_id", referencedColumnName="idUser")},
    * inverseJoinColumns={@JoinColumn(name="product_id", referencedColumnName="idProduct")}
    * )
    */
    protected $products;

    public function setProducts($products) {
        foreach($this->products as $id => $product) {
            if(!isset($products[$id])) {
                //remove from old because it doesn't exist in new
                $this->products->remove($id);
            }
            else {
                //the product already exists do not overwrite
                unset($products[$id]);
            }
        }

        //add products that exist in new but not in old
        foreach($products as $id => $product) {
            $this->products[$id] = $product;
        }    
    }
}

Now the profiler shows that doctrine only deletes specific associations (instead of all) and only inserts new associations.

However, in order to do the manual merge doctrine queries the db for all associations, which you would not have to do otherwise. In a nutshell:

Method 1

1. Delete all associations
2. Insert all associations passed in from view

Method 2

1. Select all associations
2. Delete only those associations that do not exist anymore
3. Insert only those associations from the view that did not exist before

Method 2 is better when the # of associations changed is relatively small compared to the total # of associations. However if you're changing most of your associations, Method 1 seems to be the way to go.