Symfony: Gedmo SoftDeleteable Doctrine Entities with Unique Index Columns

This is my solution to avoid constraint violation when you use Soft Delete

Here is an interesting article that explain how unique index are treated by DB
https://mariadb.com/kb/en/mariadb/getting-started-with-indexes/

The field deletedAt as Datetime with NULL value can’t be part of a unique index with NULL value, because you can have duplicate entries with NULL value.

To avoid this problem I added a field called deleted as integer with default value set as 0. When I remove with Soft Delete I set this value equal to the primary key.
So we can add deleted field partecipating in the unique index.

http://intelligentbee.com/blog/2015/01/09/symfony2-gedmo-softdeletable-doctrine-entities-with-unique-index-columns/

http://atlantic18.github.io/DoctrineExtensions/doc/softdeleteable.html

EXAMPLE

app/config/services.yml

services:
    ...
    app.soft_delete_listener:
        class: AppBundle\Events\SoftDeleteListener
        tags:
            - { name: doctrine.event_listener, event: preSoftDelete, priority: 10 }

 

AppBundle\Events\SoftDeleteListener.php

<?php
namespace AppBundle\Events;

use Doctrine\Common\Persistence\Event\LifecycleEventArgs;
use Gedmo\Sortable\Sortable;

class SoftDeleteListener
{
    public function preSoftDelete(LifecycleEventArgs $event)
    {
        $entity = $event->getObject();
        if(method_exists($entity, "getId") && method_exists($entity, "setDeleted")
        {
            $deleted = $entity->getId();
            $entity->setDeleted($deleted);

            $om = $event->getObjectManager();
            $om->persist($entity);
            $om->flush();
        }
    }
}

AppBundle\Entity\User.php

<?php
namespace AppBundle\Entity;

use Doctrine\ORM\Mapping as ORM;

use Gedmo\Mapping\Annotation as Gedmo; // gedmo annotations
use Doctrine\ORM\Mapping\UniqueConstraint;
use Doctrine\Common\Collections\ArrayCollection;

 
/**
 * User
 *
 * @ORM\Table(name="users", uniqueConstraints={@UniqueConstraint(name="item_unique", columns={"username", "deleted"})})
 * 
 * @ORM\Entity(repositoryClass="AppBundle\Repository\UserRepository")
 * @Gedmo\SoftDeleteable(fieldName="deletedAt", timeAware=false)
 * 
 */
class User
{
 /**
 * @var int
 *
 * @ORM\Column(name="id", type="integer")
 * @ORM\Id
 * @ORM\GeneratedValue(strategy="AUTO")
 */
 private $id;

 /**
 * @var string
 *
 * @ORM\Column(name="username", type="string", length=25)
 */
 private $username;

 /**
 * @var \DateTime
 *
 * @ORM\Column(name="deleted_at", type="datetime", nullable=true)
 */
 private $deletedAt;

 /**
 * @var int 
 * 
 * @ORM\Column(name="deleted", type="integer", nullable=false, options={"default":0})
 */
 private $deleted = 0;


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


 /**
 * Set username
 *
 * @param string $username
 *
 * @return User
 */
 public function setUsername($username)
 {
 $this->username = $username;

 return $this;
 }

 /**
 * Get username
 *
 * @return string
 */
 public function getUsername()
 {
 return $this->username;
 }

 
 /**
 * Set deletedAt
 *
 * @param \DateTime $deletedAt
 *
 * @return User
 */
 public function setDeletedAt($deletedAt)
 {
 $this->deletedAt = $deletedAt;

 return $this;
 }

 /**
 * Set deleted
 *
 * @param int $deleted
 *
 * @return User
 */
 public function setDeleted($deleted)
 {
 $this->deleted = $deleted;

 return $this;
 }
 
 /**
 * Get deletedAt
 *
 * @return \DateTime
 */
 public function getDeletedAt()
 {
 return $this->deletedAt;
 }

 /**
 * Get deleted
 *
 * @return int
 */
 public function getDeleted()
 {
 return $this->deleted;
 }
 
}
This entry was posted in PHP, Symfony and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s