Symfony2 datetime queryBuilder

Question

I have 2 DateTime classes in Symfony2 project. I have entity Stat, in which have $date property.

/**
 * @ORM\Column(type="date", length="11")
 */
protected $date;

I have to make queries using DateTime objects in createQueryBuilder. How can i do that ? For example:

$date_from = new DateTime('2012-02-01');
$date_to = new DateTime('2012-02-15');

I need to get all rows from table stats (entity Stat) between $date_from and $date_to. How should i write my query with createQueryBuilder ? My current code is:

$qb = $em->createQueryBuilder();
$query = $qb->select('s')
            ->from('ACME\MyBundle\Entity\Stat', 's')
            ->where('s.date >= :date_from')
            ->andWhere('s.date <= :date_to')
            ->setParameter('date_from', $date_from)
            ->setParameter('date_to', $date_to)
            ->getQuery();

Answer 1

Benjamin's answer is correct, but it lacks one detail. This is the correct way to do it:

$qb->andWhere($qb->expr()->between('s.date', ':date_from', ':date_to'));

But to set parameters, I need to do like this:

$qb->setParameter('date_from', $date_from, \Doctrine\DBAL\Types\Type::DATETIME);
$qb->setParameter('date_to', $date_to, \Doctrine\DBAL\Types\Type::DATETIME);

If I omit the DATETIME types, I get the following error (see here):

Object of class DateTime could not be converted to string

I am using Doctrine DBAL 2.0.5, this behaviour might have changed in later versions of Doctrine.

Answer 2

I had a similar situation. I couldn't use ->setParameter because of how my code was built, so i put this $andX variable to "catch" all the conditions founded in the foreach loop (in this case i just wrote the one with the dates because other are not relevant right now).

    $this->qb = $this->em->createQueryBuilder();
    $andX = $this->qb->expr()->andX();
    $this->qb->select('u')
    ->from('models\User','u');          

        foreach($data as $key=>&$value){

            if($key == 'date'){

               $from = $this->qb->expr()->gte('u.date_from',$this->qb->expr()->literal($value['datefrom']));
               $to = $this->qb->expr()->lte('u.date_to',$this->qb->expr()->literal($value['dateto']));
               $condition = $from. ' AND ' .$to;

               $andX->add($condition);
             }

      //other if

         }

Notice that the parameter for this function is a multidimensional array. Hope this is helpful.

Answer 3

A QueryBuilder is a good solution.

But you can use

->andWhere($qb->expr()->between('s.date', ':date_from', 'date_to'))

or

->andWhere($qb->expr()->andX(array(
    $qb->expr()->gte('s.date', ':date_from'),
    $qb->expr()->lte('s.date', ':date_to'))
)

The $qb->expr()->andX is usefull if you don't want to have some WTF when you add some andWhere or orWhere.

Here you have doctrine2 documentation for queryBuilder

You can also use setParameters method for your parameters

->setParameters(array(
    'date_from' => $date_from,
    'date_to'   => $date_to,
))