CODEWITHSUNDEEP
phpdoctrine-ormdoctrine
nas
nas

Reputation: 2417

How to add DATE to doctrine query?

I have a date field in Y-m-d H:i:s format. Now user can search by using date only. I have query like this:

$qb->andWhere('DATE(vc.updatedDatetime) >= :startDate AND DATE(vc.updatedDatetime) <= :endDate')
->setParameter('startDate', $filterArray['startDate'])
->setParameter('endDate', $filterArray['endDate']);

I have added following lines at my doctrine.yaml file.

    dql:
        string_functions:
            DATE: DoctrineExtensions\Query\Mysql\Date

But I am getting following error:

Attempted to load class "Date" from namespace "DoctrineExtensions\Query\Mysql".
Did you forget a "use" statement for e.g. "Twig_Extensions_Extension_Date" or "Symfony\Component\Validator\Constraints\Date"?

Upvotes: 2

Views: 1378

Answers (1)

yivi
yivi

Reputation: 47308

While you could create your own extension to add the DATE function to Doctrine, or download something like beberlei/DoctrineExtensions to do it for you, in the end is a waste of time. You do not need it.

You could simply do:

$qb
    ->andWhere('vc.updatedDatetime > :startDate')
    ->andWhere('vc.updatedDatetime < :endDate')
    ->setParameter('startDate', $startDate)
    ->setParameter('endDate', $endDate)

For your start date, you need nothing but to use the > operator. A date time string without a time part will be treated as a datetime with the time part set to 00:00:00.

For the end date, you simply need to use the < operator, and add one day to whatever date the user provides.

E.g.

$endDate = (new DateTime($filterArray['endDate']))->add(new DateInterval('P1D'))

Assuming the provided date is in "YYYY-MM-DD" format, which seems to be the case because of how you are using your code, the above would suffice.

Upvotes: 2

Related Questions