Check if date already exists in another time period

Question

I have a page where an admin can create new periods of time. Now when the admin wants to add a new period I would like to check if there isn't another period already.

This is my current code, but I guess <= and >= don't work like they should in doctrine?

$formMapper->getFormBuilder()->addEventListener(FormEvents::PRE_SUBMIT, function(FormEvent $event) {
  try {
    $from = str_replace('/', '-', $event->getData()['from']);
    $to = str_replace('/', '-', $event->getData()['to']);

    $dateFrom = new \DateTime($from, new \DateTimeZone('Europe/Amsterdam'));
    $dateFrom = $dateFrom->format('Y-d-m');

    $dateTo = new \DateTime($to, new \DateTimeZone('Europe/Amsterdam'));
    $dateTo = $dateTo->format('Y-d-m');

    $query = $this->getModelManager()->createQuery('PeriodeBundle:Periode', 'p');

    $query->select()
      ->where(':from = p.from')
      ->andWhere(':to = p.to')
      ->setParameter('from', $dateFrom)
      ->setParameter('to', $dateTo);

    $data = $query->execute();

    if (count($data) > 0) {
      $event->getForm()->addError(new FormError('This period overwrites another existing period ('
         . $data[0]->getFrom()->format('d/m/Y') . ' to '
         . $data[0]->getTo()->format('d/m/Y') . ')'));
    }
  } catch (\Exception $e) {
      $event->getForm()->addError(new FormError('Date not valid'));
  }


 });

In my database I have several periods simplified like this: period_id, from, to

Can someone point me in the right direction? My data is NULL when I create a period which already exist, and when I add a period in the far future it gives me a result of all rows. I read about lte and gte but I dont quite know how to integrate that in this code.

This code is in my Admin of my PeriodBundle.

Answer 1

I don't know how Sonata works with forms, but I think you should use validation constraint instead of listener.. Here is a usual form example:

private $em;

public function __construct(EntityManagerInterface $em)
{
    $this->em = $em;
}

public function setDefaultOptions(OptionsResolverInterface $resolver)
{
    $resolver
        ->setDefaults(array(

            ...

            'constraints' => array(
                new Callback(
                    array('callback' => array($this, 'validateForm'))
                )
            )
        ));
}

public function validateForm(MyEntity $myEntity, ExecutionContextInterface $context)
{
    $valid = true;

    $from = str_replace('/', '-', $myEntity->etFrom());
    $to = str_replace('/', '-', $myEntity->getTo());

    $dateFrom = new \DateTime($from, new \DateTimeZone('Europe/Amsterdam'));
    $dateFrom = $dateFrom->format('Y-d-m');

    $dateTo = new \DateTime($to, new \DateTimeZone('Europe/Amsterdam'));
    $dateTo = $dateTo->format('Y-d-m');

    $queryBuilder = $em->createQueryBuilder();

    $query = $queryBuilder
        ->select(array('p'))
        ->from('PeriodeBundle:Periode', 'p')
        ->where($queryBuilder->expr()->gte('p.from', ':from'))
        ->andWhere($queryBuilder->expr()->lte('p.to', ':to'))
        ->setParameters(array(
            'from' => $dateFrom,
            'to' => $dateTo
        ))
        ->getQuery();

    $data = $query->getArrayResult();

    if (count($data) > 0) {
      $valid = false;
    }

    // ....

    if (!$valid) {
        $context
            ->buildViolation('Invalid....')
            ->atPath('MYFIELD')
            ->addViolation()
        ;
    }

}