捕获“违反完整性约束：19 FOREIGN KEY约束失败”。删除受限条目时 [英] Catch "Integrity constraint violation: 19 FOREIGN KEY constraint failed" when deleting restricted entries
问题描述
问题与我使用的技术栈有关：
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（尽管RDBMS不应发挥作用）
- 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)
假设我们有两个实体：类别和产品，其中关系类别乘积为1：n，乘积类别为n：1。看起来像这样：
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
Category.php
class Category
{
// ...
/**
* @ORM\OneToMany(
* targetEntity="App\Entity\Product",
* mappedBy="category",
* cascade={"persist"}
* )
* @Assert\Valid()
*/
private $products;
// ...
}
产品。 php
Product.php
class Product
{
// ...
/**
* @ORM\ManyToOne(
* targetEntity="App\Entity\Category",
* inversedBy="samplingDocumentations"
* )
* @ORM\JoinColumn(nullable=false)
* @Assert\NotBlank()
*/
private $samplingActivity;
// ...
}
Product 到类别。 类别可以有0个或更多产品。如果类别包含任何产品，则不得删除。 类别仅在未分配产品的情况下才能删除。
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.
当我尝试删除类别时在Sonata Admin中具有 Products 的em>，按预期防止了删除操作，并引发了异常：
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
PDOException
SQLSTATE [23000]：违反完整性约束：19 FOREIGN KEY约束失败
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.
在Sonata Admin中，我使用一种解决方法，编写 CategoryAdminController 并实现 preDelete 钩子：
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.
处理此问题的最佳做法是什么？我可以在实体中实施某种验证吗？也许Doctrine事件侦听器是正确的事情？
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?
推荐答案
我设法通过添加自定义侦听器解决了该问题。删除受限制的对象时，它将捕获 ModelManagerException 。它适用于所有注册管理员。这是此类：
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);
}
}
我们注册服务：
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.
这篇关于捕获“违反完整性约束：19 FOREIGN KEY约束失败”。删除受限条目时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
