Consulting about relationship of entities in symfony(doctrine)

Question

I'll be specific. I have entities quest, city and organizer. When i try to remove organizer, that has relationship with quest error appears

An exception occurred while executing 'DELETE FROM Organizer WHERE id = ?' with params [522]:

SQLSTATE[23000]: Integrity constraint violation: 1451 Cannot delete or update a parent row: a foreign key constraint fails (test.Quest, CONSTRAINT FK_82D6D713876C4DDA FOREIGN KEY (organizer_id) REFERENCES Organizer (id))

I am absolutely confused with right way of configuring relationships. The logic is:

1. We add new cities.
2. We add new organizers and put city into organizer.
3. We create new quest and than put city and organizer into it.
4. If we remove organizer - organizers id in quests must be removed(quest must not be removed).
5. If we remove organizer - and organizers id in cities must be removed too (and city must not be removed).

I am sorry for my very dumb description, but i trying to exclude misunderstandings.

Well, what is right entity relationship should i use for my situation?

class Quest {
     ...
     /**
     * @ORM\JoinColumn(name="organizer_id", referencedColumnName="id")
     * @ORM\ManyToOne(targetEntity="AppBundle\Entity\Organizer")
     */
     protected $organizer;    
}

class Organizer {
    ...
    /**
     * @ORM\Column(type="string", length=140)
     */
    protected $name;

    /**
     * @ORM\ManyToMany(targetEntity="AppBundle\Entity\City", inversedBy="organizers")
     * @ORM\JoinTable(name="organizers_cities")
     */
    protected $cities;

    public function __construct() {
        $this->quests = new ArrayCollection(); // i don't remember why this is here
    }
}

class City {

        /**
         * @ORM\ManyToMany(targetEntity="AppBundle\Entity\Organizer", mappedBy="cities")
         */
        protected $organizers;
        /**
         * Constructor
         */
        public function __construct() {
            $this->quests =  new \Doctrine\Common\Collections\ArrayCollection();
            $this->organizers =  new \Doctrine\Common\Collections\ArrayCollection();
        }
    }

Answer 1

You have to configure Doctrine OR your DB to set the relation to null.

• With doctrine, just remove your entity so :

  $em=$this->getDoctrine()->getEntityManager(); // or getManager() in current Doctrine versions.
  $em->remove($organizer);
  $em->flush();
  

• With your DB:

  Change your JoinColumn annotation :

  class Quest {
       ...
       /**
       * @ORM\JoinColumn(nullable=false, onDelete="SET NULL",name="organizer_id", referencedColumnName="id")
       * @ORM\ManyToOne(targetEntity="AppBundle\Entity\Organizer")
       */
       protected $organizer;    
  }
  

  With this second method you will be able to use your DQL query. This method bypass doctrine, and the first is preferred.

Answer 2

I forgot about 2 important things.

1. Doctrine Lifecyle Events
2. php bin/console doctrine:generate:entities AppBundle/Entity/Quest will generate only common methods. (there was no removeOrganizer() function )

So, solution is simple, but... see below.

class Quest {
    ...
    /**
     * @ORM\ManyToOne(targetEntity="AppBundle\Entity\Organizer", inversedBy="quests")
     * @ORM\JoinColumn(name="organizer_id", referencedColumnName="id")
     */
    protected $organizer;

   ...
  public function removeOrganizer()
    {
        $this->organizer = null;
        return $this;
    }
    public function removeCity()
    {
        $this->city = null;
        return $this;
    }
}
class Organizer {
    ...
    /**
     * @ORM\ManyToMany(targetEntity="AppBundle\Entity\City", inversedBy="organizers")
     */
    protected $cities;
    /**
     * @ORM\OneToMany(targetEntity="AppBundle\Entity\Quest", mappedBy="organizer"))
     */
    protected $quests;

    public function __construct() {
        $this->quests = new ArrayCollection();
    }
     ...
    /**
     * @ORM\PreRemove()
     */
    public function removeRelationWithQuests() {
       foreach($this->getQuests() as $quest) {
            $quest->removeOrganizer()
                  ->setStatus(0);
       }
    }
}
class City {
     ...
    /**
     * @OneToMany(targetEntity="AppBundle\Entity\Quest", mappedBy="city"))
     */
    protected $quests;
    /**
     * @ORM\ManyToMany(targetEntity="AppBundle\Entity\Organizer", mappedBy="cities")
     */
    protected $organizers;
    ...
   /**
     * @ORM\PreRemove()
     */
    public function removeRelationWithQuests() {
        foreach($this->getQuests() as $quest) {
            $quest->removeCity()
                  ->setStatus(0);
        }
    }
}

As you can see I added new functions removeOrganizer and removeCity for Quest Entity and use it with event preRemove in City and Organizer Entity.

PS I have confidence that there is a better practice of solving task like this. So, i am open for criticism for you.

Answer 3

The problem is simple, you can't remove the organizer because is referenced by an existing Quest. In your case you must first remove the relations. I don't know a way of automatically do it with doctrine, but you can configure an event listener in the preRemove event, and make the Quest organizer field to null. I'm not sure if event listeners were implemented in symfony 2.1, but I hope. Also you have the option of doing the relation null directly in the controller before the remove call. The same case apply with cities. Check this documentations links:

How to Register Event Listeners and Subscribers

Doctrine Lifecycle Events

Hope this help you.