"andWhere" clause doesn't work Doctrine2

Question

I want to get some data from database table, where date field is in range between two dates. Here is my code below:

$date = new \DateTime('today');
$newDate = new \DateTime('today');
$newDate->add(new \DateInterval('P2D'));
$query = $repository->createQueryBuilder('s')
        ->select('s.day')
        ->where('s.day > :data')
        ->andWhere('s.day < :newDate')
        ->setParameter('data', $date)
        ->setParameter('newDate', $newDate)
        ->getQuery();

$dates = $query->getResult();

But unfortunately it doesn't work.

The second method gives empty array, too.

$date = new \DateTime('today');
$newDate = new \DateTime('today');
$newDate->add(new \DateInterval('P2D'));
$em = $this->getDoctrine()->getManager();
$query = $em->createQuery(
         'SELECT s.day
         FROM AppBundle:Seance s
         WHERE s.day BETWEEN :date AND :newDate')
         ->setParameter('date', $date)
         ->setParameter('newDate', $newDate);

$seances= $query->getResult();

If I remove andWhere clause or between, doctrine returns data correctyly but all recordse

Does anyone have idea why it doesn't work?

Are you sure that you have seance in that days inside your database? Try to make a more big interval for example to check it — Alessandro Minoccheri, May 24 '17 at 05:30
Yes I'm sure that table contains records I need. I tried but each time it returns empty array — bielu000, May 24 '17 at 05:38
Maybe try this: https://stackoverflow.com/a/12431539/4478443 — bnxuq, May 24 '17 at 09:16
Your code is correct. You are simply passing invalid dates. Like Doug said below, it's best you modify the time of the first date to 0,0,0 and the latter to 23,59,59. Also make sure to use >= and <= operators respectively. — Mike Doe, May 24 '17 at 10:14

score 0 · Answer 1 · answered May 24 '17 at 10:06

In my application I do what you are asking, using the following:

    /* @var $date1 \DateTime */
    $date1 = \DateTime::createFromFormat('d/m/Y', $this->request()->get("from_date"));
    $date1->format('Y-m-d h:i:s');
    $date1->setTime(0, 0, 0); // You'll likely not need this bit if times don't matter to you

    /* @var $date2 \DateTime */
    $date2 = \DateTime::createFromFormat('d/m/Y', $this->request()->get("to_date"));
    $date2->format('Y-m-d h:i:s');
    $date2->setTime(23, 59, 59); // You'll likely not need this bit if times don't matter to you

Then in it's repository we do this:

 ->andWhere("s.date >= :date1 AND s.date < :date2)

score 0 · Answer 2 · answered May 24 '17 at 10:18

You simply needed to pass correct criteria.

$dateFrom = (new \DateTime())->setTime(0, 0, 0);
$dateTo   = (new \DateTime())->add(new \DateInterval('P2D'))->setTime(23, 59, 59);

$query = $repository->createQueryBuilder('s')
    ->where('s.day >= :dateFrom', 's.day <= :dateTo')
    ->setParameter('dateFrom', $dateFrom)
    ->setParameter('dateTo', $dateTo)
    ->getQuery();

$dates = $query->getResult();

"andWhere" clause doesn't work Doctrine2

2 Answers2