QueryBuilder concat date and time

Question

I want to find a conflict between two datetimes. The problem is that in database I have Date and time field separately. So I did concat(date,time):

public function findConflictingEvents($datef,$dateu,$personID){
    $from = $datef->format('Y-m-d H:i:s');
    $until = $dateu->format('Y-m-d H:i:s');

    $qb = $this->createQueryBuilder('event');
    $qb->select('event')
        ->innerJoin('event.visitors', 'p')
        ->add('where',
            $qb->expr()->orX(
                $qb->expr()->andX(
                    $qb->expr()->lte(':until', $qb->expr()->concat('event.date_until', 
                        $qb->expr()->literal(' '), 'event.time_until')),
                    $qb->expr()->gte(':until', $qb->expr()->concat('event.date_from', 
                        $qb->expr()->literal(' '), 'event.time_from'))
                ),
                $qb->expr()->andX(
                    $qb->expr()->lte(':from', $qb->expr()->concat('event.date_until',
                        $qb->expr()->literal(' '), 'event.time_until')),
                    $qb->expr()->gte(':from', $qb->expr()->concat('event.date_from', 
                        $qb->expr()->literal(' '), 'event.time_from'))
                ),
                $qb->expr()->andX(
                    $qb->expr()->lte(':from', $qb->expr()->concat('event.date_from', 
                        $qb->expr()->literal(' '), 'event.time_from')),
                    $qb->expr()->gte(':until', $qb->expr()->concat('event.date_until', 
                        $qb->expr()->literal(' '), 'event.time_until'))
                )
            )                
        )
        ->andWhere('p.id = ?1')
        ->setParameter('from', $from)
        ->setParameter('until', $until)
        ->setParameter(1, $personID);
     return $qb->getQuery()->getResult();
}

But the query gives unexpected results. I think that I should convert to timestamp and compare then.

• How can I convert date and time to unix_timestamp in doctrine2 querybuilder?
• Or is there some other idea how to do?

Thank you in advance!

Answer 1

The bound parameters and the expression of checking datetime ranges were assigned the wrong way. Also concatenation takes two arguments, you need to use it again for the third part.

Additionally it would be better to think about your database as objects rather than tables when using Doctrine. Use objects whenever you can. Return a new ArrayCollection instead of an array and allow your repository method to accept an instance of Person and get the ID from there.

use Doctrine\Common\Collections\Collection;
use Doctrine\Common\Collections\ArrayCollection;
use Doctrine\ORM\EntityRepository;
use Doctrine\ORM\QueryBuilder;

use Your\Bundle\Entity\Event;
use Your\Bundle\Entity\Person;

class EventRepository extends EntityRepository
{
    /**
     * @param \DateTime $from
     * @param \DateTime $until
     * @param Person    $person
     *
     * @return Event[]|Collection
     */
    public function findConflictingEvents(\DateTime $from, \DateTime $until, Person $person)
    {
        $qb = $this->createQueryBuilder('event');
        $qb->select('event')
            ->innerJoin('event.visitors', 'p')
            ->add('where',
                $qb->expr()->orX(
                    $qb->expr()->andX(
                        $qb->expr()->lte(
                            $this->buildDateTimeUntilExpression($qb),
                            ':until'
                        ),
                        $qb->expr()->gte(
                            $this->buildDateTimeFromExpression($qb),
                            ':until'
                        )
                    ),
                    $qb->expr()->andX(
                        $qb->expr()->lte(
                            $this->buildDateTimeUntilExpression($qb),
                            ':from'
                        ),
                        $qb->expr()->gte(
                            $this->buildDateTimeFromExpression($qb),
                            ':from'
                        )
                    ),
                    $qb->expr()->andX(
                        $qb->expr()->lte(
                            $this->buildDateTimeFromExpression($qb),
                            ':from'
                        ),
                        $qb->expr()->gte(
                            $this->buildDateTimeUntilExpression($qb),
                            ':until'
                        )
                    )
                )
            )
            ->andWhere('p.id = ?1')
            ->setParameter('from', $from)
            ->setParameter('until', $until)
            ->setParameter(1, $person->getId());
        ;

        return new ArrayCollection($qb->getQuery()->getResult());
    }

    /**
     * @param QueryBuilder $qb
     *
     * @return mixed
     */
    private function buildDateTimeUntilExpression(QueryBuilder $qb)
    {
        return $qb->expr()->concat('event.dateUntil', $qb->expr()->concat($qb->expr()->literal(' '), 'event.timeUntil'));
    }

    /**
     * @param QueryBuilder $qb
     *
     * @return mixed
     */
    private function buildDateTimeFromExpression(QueryBuilder $qb)
    {
        return $qb->expr()->concat('event.dateFrom', $qb->expr()->concat($qb->expr()->literal(' '), 'event.timeFrom'));
    }

}