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();

Does your code not work?

Phil
– Phil

2012-02-29 05:37:41 +00:00
Commented Feb 29, 2012 at 5:37 — Phil
– Phil, Commented Feb 29, 2012 at 5:37
it works... i found problem elsewhere

artyomboyko
– artyomboyko

2012-02-29 05:47:46 +00:00
Commented Feb 29, 2012 at 5:47 — artyomboyko
– artyomboyko, Commented Feb 29, 2012 at 5:47

Community · Accepted Answer · 2020-06-20 09:12:55Z

35

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.

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Mar 12, 2013 at 7:46

Rauni Lillemets

2,4072 gold badges27 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

RiggsFolly Over a year ago

Note: Off site link is broken

Benjamin Lazarecki · Accepted Answer · 2022-09-15 08:23:45Z

10

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,
))

edited Sep 15, 2022 at 8:23

answered May 30, 2012 at 22:55

Benjamin Lazarecki

2,9701 gold badge22 silver badges27 bronze badges

1 Comment

user2019515 Over a year ago

I ran into an issue where both date_from and date_to are the same day, in that case no results where returned. The problem was that I was using DateTime, so it was looking for results on that very second (mightnight) for that day.

Limon · Accepted Answer · 2013-11-04 20:20:29Z

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.

Collectives™ on Stack Overflow

Symfony2 datetime queryBuilder

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related