Catch "Integrity constraint violation: 19 FOREIGN KEY constraint failed" when deleting restricted entries

Question

The question relates to the technology stack I use:

• Symfony 4.2.3
• Doctrine ORM 2.6.3
• Sonata Admin 3.45.2
• sqlite3 3.22 (although the RDBMS shouldn't play a role)

Let's say we have two entities: Category and Product where the relation category to product is 1:n and product to category is n:1. This would look like:

Category.php

class Category
{
    // ...
    /**
     * @ORM\OneToMany(
     *     targetEntity="App\Entity\Product",
     *     mappedBy="category",
     *     cascade={"persist"}
     * )
     * @Assert\Valid()
     */
    private $products;
    // ...
}

Product.php

class Product
{
    // ...
    /**
     * @ORM\ManyToOne(
     *     targetEntity="App\Entity\Category", 
     *     inversedBy="products"
     * )
     * @ORM\JoinColumn(nullable=false)
     * @Assert\NotBlank()
     */
    private $category;
    // ...
}

Product must be assigned to a Category. Category can have 0 or more Products. If Category contains any Products it must NOT be deleted. Category can be deleted only if no Products are assigned to it.

When I try to delete a Category which has Products in the Sonata Admin, the deletion is prevented, as expected, and an Exception is thrown:

PDOException

SQLSTATE[23000]: Integrity constraint violation: 19 FOREIGN KEY constraint failed

Now, that is expected, but not very nice for the end user. I'd like to provide a message and inform the user that the Category can not be deleted because it still holds Products.

In Sonata Admin I use a workaround, writing CategoryAdminController and implementing the preDelete hook:

public function preDelete(Request $request, $object)
{
    if ($object->getProducts()->isEmpty()) {
        return null;
    }

    $count = $object->getProducts()->count();
    $objectName = $this->admin->toString($object);
    $this->addFlash(
        'sonata_flash_error',
        sprintf(
            'The category "%s" can not be deleted because it contains %s product(s).',
            $objectName,
            $count
        )
    );

    return $this->redirectTo($object);
}

However this doesn't feel right, because I have to reimplement it outside the admin.

What is the best practice to handle this? Can I implement some kind of validation in the entity? Or maybe Doctrine event listeners are the right thing?

Answer 1

I believe what you are trying to do is described here:

Symfony + Doctrine - Define an error message when integrity constraint error

I won't copy-paste the whole message, but the concept is to create onKernelResponse listener and listen for PDOException. How to do this there are lot of articles, I believe you can easily find online, I've selected one of the first that I've found.

Within that Listener you can determine what exception it is and use flashbag either a default symfony one:

https://symfony.com/doc/current/components/http_foundation/sessions.html

$session->getFlashBag()->add('notice', 'Profile updated');

Or you could use Sonata Core Flashbag:

https://sonata-project.org/bundles/core/master/doc/reference/flash_messages.html

To use this feature in your PHP classes/controllers:

$flashManager = $this->get('sonata.core.flashmessage.manager');

$messages = $flashManager->get('success'); To use this feature in your templates, include the following template (with an optional domain parameter):

{% include '@SonataCore/FlashMessage/render.html.twig' %}

Note If necessary, you can also specify a translation domain to override configuration here:

{% include '@SonataCore/FlashMessage/render.html.twig' with { domain: 'MyCustomBundle' } %}

You can also take a look at this article https://tocacar.com/symfony2-how-to-modify-sonataadminbundles-error-message-on-entity-deletion-ca77cac343fa and override CRUDController::deleteAction so you can handle such errors.

And here you can find some code, that is related to your issue a bit, on Sonata Admin GitHub page https://github.com/sonata-project/SonataAdminBundle/issues/4485 it catches PDOException, so also check what version you are using, maybe what you need is an update.

Answer 2

I managed to solve the problem by adding a custom listener. It catches the ModelManagerException when deleting a restricted object. It works for all registered admins. Here is the class:

<?php

namespace App\EventListener;

use Symfony\Component\HttpFoundation\Session\SessionInterface;
use Symfony\Component\Routing\Generator\UrlGeneratorInterface;
use Doctrine\ORM\EntityManagerInterface;
use Symfony\Component\HttpKernel\Event\GetResponseForExceptionEvent;
use Symfony\Component\HttpFoundation\RedirectResponse;
use Sonata\AdminBundle\Exception\ModelManagerException;

class ModelManagerExceptionResponseListener
{
    private $session;
    private $router;
    private $em;

    public function __construct(SessionInterface $session, UrlGeneratorInterface $router, EntityManagerInterface $em)
    {
        $this->session = $session;
        $this->router = $router;
        $this->em = $em;
    }

    public function onKernelException(GetResponseForExceptionEvent $event)
    {
        // get the exception
        $exception =  $event->getException();
        // we proceed only if it is ModelManagerException
        if (!$exception instanceof ModelManagerException) {
            return;
        }

        // get the route and id
        // if it wasn't a delete route we don't want to proceed
        $request = $event->getRequest();
        $route = $request->get('_route');
        $id = $request->get('id');
        if (substr($route, -6) !== 'delete') {
            return;
        }
        $route = str_replace('delete', 'edit', $route);

        // get the message
        // we proceed only if it is the desired message
        $message = $exception->getMessage();
        $failure = 'Failed to delete object: ';
        if (strpos($message, $failure) < 0) {
            return;
        }

        // get the object that can't be deleted
        $entity = str_replace($failure, '', $message);
        $repository = $this->em->getRepository($entity);
        $object = $repository->findOneById($id);

        $this->session->getFlashBag()
            ->add(
                'sonata_flash_error',
                sprintf('The item "%s" can not be deleted because other items depend on it.', $object)
            )
        ;

        // redirect to the edit form of the object
        $url = $this->router->generate($route, ['id' => $id]);
        $response = new RedirectResponse($url);
        $event->setResponse($response);
    }
}

And we register the service:

app.event_listener.pdoexception_listener:
    class: App\EventListener\ModelManagerExceptionResponseListener
    arguments:
        - '@session'
        - '@router'
        - '@doctrine.orm.entity_manager'
    tags:
        - { name: kernel.event_listener, event: kernel.exception }
    public: true # this maybe isn't needed

Probably deleting of any object outside the admin will not be allowed in my particular case. Therefore this solution satisfies the requirements. I hope that this example can help others. You'll have to adapt some parts according to your needs.