Doctrine DQL Date作为参数问题

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

大家好，我有一个可行的DQL（我从一开始就得到了所有的活动）：

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 :

我的error.log有这一行：

 #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.

这个日期的print_r给了我：2011-08-01 00:00:00这是正确的。

Here is the mapping for date:

这是日期的映射：

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

Any help appreciated thanks !

任何帮助表示感谢！

4 个解决方案

#1

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

使用查询参数，然后将DateTime对象设置为参数的值。然后，Doctrine将自动处理转换。

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

#2

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

#3

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

我认为这可能是因为您的e.date被映射为日期时间。尝试传递DateTime对象而不是时间戳。

//...
->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"))

#4

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

对我来说，这个有用，可以在最后30分钟内获得所有结果：

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

#1