mysql - Symfony 约束以防止在重叠时间范围内重复

Question

如何使用 Doctrine ORM 在 Symfony 中强制重叠日期范围内的值的唯一性。
我有以下实体

<?php
/**
 * @ORM\Entity
 * @ORM\Table("tax_component")
 */

class TaxComponent
{
    /**
     * @ORM\Id()
     * @ORM\GeneratedValue()
     * @ORM\Column(name="tax_component_id", type="integer")
     */
    private ?int $id;

    /**
     * @ORM\Column(name="tax_component_name", type="string", length=20)
     */
    private string $name;

    /**
     * @ORM\Column(name="tax_component_rate", type="integer")
     * @Assert\GreaterThanOrEqual(0)
     */
    private int $rate;

    /**
     * @ORM\Column(name="tax_component_applicable_from", type="datetime_immutable")
     */
    private DateTimeInterface $applicableFrom;

    /**
     * @ORM\Column(name="tax_component_applicable_to", type="datetime_immutable")
     */

    public function __construct(string $name, int $rate, ?DateTimeImmutable $applicableFrom = null, ?DateTimeImmutable $applicableTo = null)
    {
        ...
    }

}

我要制作 $name唯一的重叠时间范围 $applicableFrom和 $applicableTo .例如，

$repository->save(
    new TaxComponent('inter-state', 1800, new DateTime('2018-04-01:00:00:00'), new DateTime('2019-03-31T23:59:59'))
);

// The following should be allowed since there is no overlap between the two time ranges using the name 'inter-state'
$repository->save(
    new TaxComponent('inter-state', 1200, new DateTime('2019-04-01:00:00:00'), new DateTime('2020-03-31T23:59:59'))
);

// The following should fail since 'inter-state' is ambiguous during the period 2019-09-01:00:00:00 to 2020-03-31T23:59:59
$repository->save(
    new TaxComponent('inter-state', 1800, new DateTime('2019-09-01:00:00:00'), new DateTime('2020-09-31T23:59:59'))
);

是否有约束来强制执行这是 Symfony？
我正在计划从 TaxComponentRepository::save 中检查现有实体, 之前调用 $this->entityManager->persist .有更好的解决方案吗？

Answer 1

更简洁的方法是创建您自己的自定义断言。
从创建约束开始:

<?php

namespace App\Validator\Constraints;

use Symfony\Component\Validator\Constraint;

/**
 * @Annotation
 */
class TaxComponentConstraint extends Constraint
{
    public $message = 'Another tax component overlap this one: {{ taxComponent}}';

    public function getTargets()
    {
        return self::CLASS_CONSTRAINT;
    }

    public function validatedBy()
    {
        return 'App\Validator\Constraints\TaxComponentValidator';
    }
}

现在您必须创建一个验证器来检查是否存在与两个税收组成部分的重叠。

<?php

namespace App\Validator\Constraints;

use Symfony\Component\Validator\Constraint;
use Symfony\Component\Validator\ConstraintValidator;
use Symfony\Component\Validator\Exception\UnexpectedTypeException;

class TaxComponentValidator extends ConstraintValidator
{
    public function validate($taxComponentObject, Constraint $constraint)
    {
    //Check however you want if the tax component can be created (So no overlap between two existing TaxComponent)
    if($overlappingTaxComponent){
        $this->context->buildViolation($constraint->message)
                        ->setParameter('{{ taxComponent }}', $overlappingTaxComponent->__toString())
                        ->addViolation();
    }

    }
}

在这里， $overlappingTaxComponent 是一个 TaxComponent，因为您的限制而阻止我们制作一个。
如果约束正确完成，您现在可以在实体中轻松使用它，以便在提交表单时自动检查:

<?php

//...
use App\Validator\Constraints as CustomAssert;

/**
 * @ORM\Entity
 * @ORM\Table("tax_component")
 * @CustomAssert\TaxComponentConstraint
 */
class TaxComponent
{
    /**
     * @ORM\Id()
     * @ORM\GeneratedValue()
     * @ORM\Column(name="tax_component_id", type="integer")
     */
    private ?int $id;

    /**
     * @ORM\Column(name="tax_component_name", type="string", length=20)
     */
    private string $name;