Doctrine DQL Date as parameter problem

Question

Hi there I got a DQL that works (I get all my event since the beginning) :

DoctrineHelper::getEntityManager()->createQueryBuilder()
    ->select("u.surname, count(u.surname) as total")
    ->from("User", "u")
    ->from("AbstractEvent", "e")
    ->from("Attendance", "a")
    ->where("u = a.attendee")
    ->andWhere("e = a.event")
    ->andWhere("a.status=1")
    ->andWhere("e.date<CURRENT_TIMESTAMP()")
    ->groupBy("u.email")
    ->orderBy("total","desc");

But this one doesn't (I would like only this month event):

DoctrineHelper::getEntityManager()->createQueryBuilder()
    ->select("u.surname, count(u.surname) as total")
    ->from("User", "u")
    ->from("AbstractEvent", "e")
    ->from("Attendance", "a")
    ->where("u = a.attendee")
    ->andWhere("e = a.event")
    ->andWhere("a.status=1")
    ->andWhere("e.date<CURRENT_TIMESTAMP()")
    ->andWhere("e.date>?", date('Y-m-d 00:00:00', strtotime('-'.(date('j')-1).' day')) )
    ->groupBy("u.email")
    ->orderBy("total","desc");

My error.log has this line :

 #0 /var/www/Doctrine/ORM/Query/AST/InputParameter.php(46): Doctrine\\ORM\\Query\\QueryException::invalidParameterFormat('?')

A print_r of the date gives me : 2011-08-01 00:00:00 which is correct.

Here is the mapping for date:

/**
     * Date of the event.
     * @Column(type="datetime")
     */
    private $date;

Any help appreciated thanks !

Rident

Answer 1

Couldn'y make it work with the parameter so here is what I came to finally :

$now = new DateTime;
$now->modify( '-'.(date('j')-1).' day' );

$qb = DoctrineHelper::getEntityManager()->createQueryBuilder()
                        ->select("u.surname, count(u.surname) as total")
                        ->from("User", "u")
                        ->from("AbstractEvent", "e")
                        ->from("Attendance", "a")
                        ->where("u = a.attendee")
                        ->andWhere("e = a.event")
                        ->andWhere("a.status=1")
                        ->andWhere("e.date<CURRENT_TIMESTAMP()")
                        ->andWhere("e.date > '".$now->format("Y-m-d H:i:s")."'")
                        ->groupBy("u.email")
                        ->orderBy("total","desc");

Rident

Answer 2

Use a query parameter and then set the DateTime object as value of the parameter. Doctrine will then handle the conversion automatically.

Here is the example code from my own project

    $builder = $this->getEntityManager()->createQueryBuilder();
    $builder->select('a')
        ->from('MaggotsLeadGeneratorBundle:JobApplication', 'a')
    ;

    $and = $builder->expr()->andX();
    if ($dateFrom) {
        $and->add($builder->expr()->gt('a.ctime', ':dateFrom'));
        $builder->setParameter('dateFrom', $dateFrom);
    }

    if ($dateTo) {
        $and->add($builder->expr()->lt('a.ctime', ':dateTo'));
        $builder->setParameter('dateTo', $dateTo);
    }

    $builder->where($and);
    $query = $builder->getQuery();

    $result = $query->getResult();

Avilla

Thanks for feedback, I'll try this proposition when I got two minutes to replace my "dirty" solution. – Rident

Even better with $and=$builder->expr()->andX(); – Razo

What is $and? It's not declared in the snippet. Please, add it. – Chockablock

Answer 3

I think it could be because your e.date is mapped as datetime. Try passing DateTime object instead of timestamp.

//...
->andWhere("e.date>?", new DateTime("2011-08-01 00:00:00"))
//...

Please, post your entity mapping, this would help clarifying the source of your error.

Update: strange... Maybe this will work:

->andWhere("e.date>'?'", new DateTime("2011-08-01 00:00:00")->format("Y-m-d H:i:s"))

Underthrust

DateTime gives me : /var/www/Doctrine/ORM/Query/Expr/Base.php(52): Doctrine\\ORM\\Query\\Expr\\Base->add(Object(DateTime)) in error log. I'm going to add mapping in my question. – Rident

Your last proposition gave me : Doctrine\ORM\Query\Parser.php(2647): Doctrine\ORM\Query\Parser->syntaxError('=, <, <=, <>, >...'). So I modified according to my answer to do the trick. I can't give you correct answer but You got a +1 for helping me thinking of String. – Rident

Answer 4

For me this one worked, to get all results within the last 30 minutes:

$qb = $em->createQueryBuilder();
$qb->select('u')
    ->from('User', 'u')
    ->where('u.group = '.$this->group->getId())
    ->andWhere("u.createdAt > :date")
    ->setParameter('date', new \DateTime(date("F d Y H:i:s", time() - 30*60)));

Confirmatory

Answer 5

I had a similar problem using setParameter with a DateTime object. Some searching lead me to this old bug report ...

https://github.com/doctrine/orm/issues/8113

Notice the author mentions the third argument to setParameter. When I set up my Entity, the console set the column type to Types::DATE_MUTABLE so I figured that would be a reasonable parameter. This is what worked for me:

->setParameter('mydate', $myDateTime, Types::DATE_MUTABLE)

Note the class constant Types::DATE_MUTABLE is just an alias for 'date' so I changed my code to look like this:

->setParameter('mydate', $myDateTime, 'date')

Removing the third value there causes my query to return null with no errors.

The Doctrine documentation offers some details about this under Binding Parameters.

https://www.doctrine-project.org/projects/doctrine-orm/en/3.1/reference/query-builder.html#binding-parameters-to-your-query

Andyane