Symfony2 Custom Constraint on overlap date

Question

I have a doctrine entity as describe below:

company\MyBundle\Entity\ProgramGrid:
    type: entity
    table: program_grid
    id:
        id_program_grid:
            type: integer
            generator: {strategy: IDENTITY}
    fields:
        name:
            type: text
            nullable: true
        start_date:
            type: date
            nullable: false
        end_date:
            type: date
            nullable: true

I woud like add a validation constraint witch validate that start_date and end_date will not overlap with another record.

If I have 2 records A and B, I want:

B.start_date > A.end_date

What is the best way to achieve that?

Answer 1

I just implemented such a constraint and its validator. This is what it looks like:

Constraint:

<?php

namespace AppBundle\Validator\Constraints;

use Symfony\Component\Validator\Constraint;
use Symfony\Component\Validator\Exception\ConstraintDefinitionException;

/**
 * @Annotation
 */
class NotOverlapping extends Constraint
{
    public $message = 'This value overlaps with other values.';

    public $service = 'app.validator.not_overlapping';

    public $field;

    public $errorPath;

    public function getRequiredOptions()
    {
        return ['field'];
    }

    public function getDefaultOption()
    {
        return 'field';
    }

    /**
     * The validator must be defined as a service with this name.
     *
     * @return string
     */
    public function validatedBy()
    {
        return $this->service;
    }

    /**
     * @return string
     */
    public function getTargets()
    {
        return self::CLASS_CONSTRAINT;
    }
}

Validator:

<?php

namespace TriprHqBundle\Validator\Constraints;

use Doctrine\Common\Collections\Criteria;
use Doctrine\Common\Persistence\ManagerRegistry;
use League\Period\Period;
use Symfony\Component\Validator\Exception\UnexpectedTypeException;
use Symfony\Component\Validator\Exception\ConstraintDefinitionException;
use Symfony\Component\Validator\Constraint;
use Symfony\Component\Validator\ConstraintValidator;

class NotOverlappingValidator extends ConstraintValidator
{
    /**
     * @var ManagerRegistry
     */
    private $registry;

    /**
     * NotOverlappingValidator constructor.
     * @param ManagerRegistry $registry
     */
    public function __construct(ManagerRegistry $registry)
    {
        $this->registry = $registry;
    }

    /**
     * @param object     $entity
     * @param Constraint $constraint
     *
     * @throws UnexpectedTypeException
     * @throws ConstraintDefinitionException
     */
    public function validate($entity, Constraint $constraint)
    {
        if (!$constraint instanceof NotOverlapping) {
            throw new UnexpectedTypeException($constraint, __NAMESPACE__.'\NotOverlapping');
        }

        if (!is_null($constraint->errorPath) && !is_string($constraint->errorPath)) {
            throw new UnexpectedTypeException($constraint->errorPath, 'string or null');
        }

        $em = $this->registry->getManagerForClass(get_class($entity));

        if (!$em) {
            throw new ConstraintDefinitionException(sprintf('Unable to find the object manager associated with an entity of class "%s".', get_class($entity)));
        }

        /* @var $class \Doctrine\Common\Persistence\Mapping\ClassMetadata */
        $class = $em->getClassMetadata(get_class($entity));

        if (!array_key_exists($constraint->field, $class->embeddedClasses)) {
            throw new ConstraintDefinitionException(sprintf(
                'The field "%s" is not a Doctrine embeddable, so it cannot be validated for overlapping time periods.',
                $constraint->field
            ));
        }

        $value = $class->reflFields[$constraint->field]->getValue($entity);

        if (!is_null($value) && !($value instanceof Period)) {
            throw new UnexpectedTypeException($value, 'null or League\Period\Period');
        }

        if(is_null($value)) {
            return;
        }

        // ... WHERE existing_start < new_end
        //       AND existing_end   > new_start;
        $criteria = new Criteria();
        $criteria
            ->where($criteria->expr()->lt(sprintf('%s.startDate', $constraint->field), $value->getEndDate()))
            ->andWhere($criteria->expr()->gt(sprintf('%s.endDate', $constraint->field), $value->getStartDate()))
        ;

        $repository = $em->getRepository(get_class($entity));
        $result = $repository->matching($criteria);

        if ($result instanceof \IteratorAggregate) {
            $result = $result->getIterator();
        }

        /* If no entity matched the query criteria or a single entity matched,
         * which is the same as the entity being validated, there are no
         * overlaps.
         */
        if (0 === count($result) || (1 === count($result) && $entity === ($result instanceof \Iterator ? $result->current() : current($result)))) {
            return;
        }

        $errorPath = $constraint->errorPath ?: $constraint->field;

        $this->context->buildViolation($constraint->message)
            ->atPath($errorPath)
            ->addViolation()
        ;
    }
}

You can find it together with an example entity in my gist.

Answer 2

The answer to your problem are Events.

You need to create an event subscriber (as described in the Symfony Docs) for the pre persist event.

In that event subscriber you must query your table and see if you have an overlapping range. The best answer for that algorithm is found in the accepted answer of this question: Determine Whether Two Date Ranges Overlap