Symfony Doctrine2 manyToMany relationship not removed - Specific to SQLite

Question

I have several classes using a Taggable trait to set up a tag system common to several doctrine entities (Project, Note, ...).

The relationship between these entities and these tags is a ManyToMany relationship that I can not make multi-directional.

My problem: When I delete a Project entity, it is removed from the project table, but the relationships in the project_tag table between this project and the tags are not deleted. Then, if I create a new Project entity, an exception is thrown.

An exception exists while executing 'INSERT INTO project_tag (project_id, tag_id) VALUES (?,?)' With params [2, 4]:

SQLSTATE [23000]: Integrity constraint violation: 19 UNIQUE constraint failed: project_tag.project_id, project_tag.tag_id

Entities :

Tag

/**
 * Tag
 *
 * @ORM\Table(name="tag")
 * @ORM\Entity(repositoryClass="AppBundle\Repository\TagRepository")
 */
class Tag
{
    /**
     * @var int
     *
     * @ORM\Column(name="id", type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    private $id;

    /**
     * @var string
     *
     * @ORM\Column(name="name", type="string", length=255, unique=true)
     */
    private $name;

    /**
     * @ORM\Column(name="last_use_at", type="datetime", nullable=false)
     * @var \DateTime
     */
    private $lastUseAt;


    public function __construct()
    {
        $this->lastUseAt = new \DateTime();
    }

    public function __toString()
    {
        return $this->name;
    }

    /**
     * Get id
     *
     * @return int
     */
    public function getId()
    {
        return $this->id;
    }

    /**
     * Set name
     *
     * @param string $name
     *
     * @return Tag
     */
    public function setName($name)
    {
        $this->name = $name;

        return $this;
    }

    /**
     * Get name
     *
     * @return string
     */
    public function getName(): string
    {
        return $this->name;
    }

    /**
     * @return \DateTime
     */
    public function getLastUseAt(): \DateTime
    {
        return $this->lastUseAt;
    }

    /**
     * @param \DateTime $lastUseAt
     */
    public function setLastUseAt(\DateTime $lastUseAt)
    {
        $this->lastUseAt = $lastUseAt;
    }
}

Taggable

trait Taggable
{

 /**
 * @var ArrayCollection
 *
 * @ORM\ManyToMany(targetEntity="AppBundle\Entity\Tag", cascade={"persist"})
 */
protected $tags;

/**
 * Add tag
 *
 * @param Tag $tag
 *
 * @return $this
 */
public function addTag(Tag $tag)
{
    $tag->setLastUseAt(new \DateTime());
    $this->tags[] = $tag;

    return $this;
}

/**
 * Remove tag
 *
 * @param Tag $tag
 */
public function removeTag(Tag $tag)
{
    $this->tags->removeElement($tag);
}

/**
 * Get tags
 *
 * @return \Doctrine\Common\Collections\Collection
 */
public function getTags()
{
    return $this->tags;
}
}

Project

/**
 * Project
 *
 * @ORM\Table(name="project")
 * @ORM\Entity(repositoryClass="AppBundle\Repository\ProjectRepository")
 */
class Project
{
    use Taggable;
}

Note

class Note
{
    use Taggable;
}

Is this the only solution or is my annotation incomplete / incorrect? I tried with JoinColumns, JoinTable and onDelete = "cascade" but nothing works.

In the meantime, I dodged the problem with this instruction placed before the suppresion.

$project->getTags()->clear();

Full code of the action in the controller :

/**
 * @Route("/project/{id}/delete", name="project_delete")
 */
public function deleteAction($id) {
    $em = $this->getDoctrine()->getManager();
    $project = $em->getRepository('AppBundle:Project')->find($id);

    if(!$project) {
        return $this->redirectToRoute('index');
    }

    $project->getTags()->clear();
    $em->remove($project);
    $em->flush();

    return $this->redirectToRoute('index');
}

Answer 1

I think I found a better solution: you can set the PRAGMA within Doctrine configuration. Like:

doctrine:
    dbal:
        # configure these for your database server
        driver: 'pdo_sqlite'
        #server_version: '5.7'
        #charset: utf8mb4
        #default_table_options:
            #charset: utf8mb4
            #collate: utf8mb4_unicode_ci

        url: '%env(resolve:DATABASE_URL)%'
        options:
            'PRAGMA foreign_keys': 'ON'

I just tried it on my Symfony 4 application, re-created the database and tested using DB Browser for SQLite and it works as I expected.

Hope this helps

Answer 2

I managed to fix the problem. Here's my solution working for SQLite conections.

Create an eventListener listening on the kernel.request event :

namespace AppBundle\EventListener;


use Doctrine\Bundle\DoctrineBundle\Registry;
use Doctrine\Common\Persistence\ObjectManager;
use Symfony\Component\HttpKernel\Event\GetResponseEvent;

class RequestListener
{
    /**
     * @var Registry
     */
    private $doctrine;

    public function __construct(Registry $doctrine)
    {
        $this->doctrine = $doctrine;
    }

    public function onKernelRequest(GetResponseEvent $event)
    {
        $this->doctrine->getConnection()->exec('PRAGMA foreign_keys = ON');
    }
}

Service declaration

  app.event_listener.request_listener:
        class: AppBundle\EventListener\RequestListener
        arguments:
            - '@doctrine'
        tags:
            - { name: kernel.event_listener, event: kernel.request }

Answer 3

I think the problem is that you have your trait Taggable set as the owning side of the ManyToMany relationship but your are deleting the inverse side and expecting something to happen as a result. Doctrine will only check the owning side of the relationship in order to persist any changes. See here for docs on this.

You can solve by making the Taggable the inverse side of each of your relationships, or by manually telling doctrine to delete the owning side.

The first solution will probably not work for you since you won't (easily) specify multiple inverse sides. (Are you sure a trait is the right way to go for this??)

The second solution is easy. In your entities like Project for your deleteTag($tag) function, call a delete function on the owning side (e.g., deleteProject($project). You will have to create if one does not exist.

class Project
{
    use Taggable;

    public function deleteTag($tag)
    {
        $this->tags->removeElement($tag);

        // persist on the owning side
        $tag->deleteProject($this);
    }
}

EDIT:

After seeing full code, it looks like you are deleting correctly. Now you need to tell doctrine to carry that through. See this post for full details, but basically you can change your trait to this:

trait Taggable
{

     /**
      * @var ArrayCollection
      *
      * @ORM\ManyToMany(
      *     targetEntity="AppBundle\Entity\Tag", 
      *     cascade={"persist"},
      *     onDelete="CASCADE"
      * )
 */
protected $tags;

// ...
}