Doctrine的日期字段 - 如何编写查询 [英] Doctrine's date field - how to write query

问题描述

我正在使用Symfony2和Doctrine。

我有一个日期字段，这里是：

  / ** 
 * @ ORM\Column（type =date）
 * / 
 protected $ date; 
  

在我的表单中，我使用一个文本字段来避免Chrome默认的日期戳，但是我将DateTime对象插入到数据库：

  if（$ request-> isMethod（'POST'））{
 $ form-> bind（$ request）; 
 
 //将字符串日期转换为DateTime对象，并将其作为对象发送到数据库
 $ dateObj = \DateTime :: createfromformat（'dm-Y'，$ expense-> getDate ））; 
 $ expense-> setDate（$ dateObj）; 
 // ... 
  

然后我想查找具有特定日期的所有项目：

  public function findExpensesForDate（$ user，$ date）
 {
 $ q = $ this 
  - > createQueryBuilder（'e'）
  - >其中（'e.date =：date'）
  - > andWhere（'e.user =：user'）
  - > setParameter（'date'，$ date）
  - > setParameter（'user'，$ user）
  - > getQuery（）; 
 
 return $ q-> getResult（）; 
} 
  

并调用如下：

  $ expenses_for_today = $ this-> repository-> findExpensesForDate（$ this-> user，$ today）; 
  

当

  $ today = new / DateTime（）; 
  

并在

<$ p $时返回结果p> $ today_obj = new / DateTime（）;
$ today = $ today_obj->格式（'Y-m-d'）;


那么为什么当我把日期作为对象这不行？是不是使用date date的原因是利用DateTime对象进行查询？我想我错过了一些琐碎而重要的事情，但是我看不出什么，或者我不太了解情况。我的理解是这样的：字段是类型date，所以我应该在其中插入DateTime对象，当quering我也应该是DateTime对象。你可以帮我解决这个问题吗？

PS：我尝试将字段更改为datetime：

  / ** 
 * @ ORM\Column（type =datetime）
 * / 
 protected $ date; 
  

但没有变化。

在字符串中查询是否正常？

解决方案

我认为这是因为DateTime对象太具体了并且还有小时，分钟和秒，所以它不能等于数据库中的注册日期！

如果要使用DateTime对象，您需要获取一天开始的日期和结束日期，以获得当天的所有结果！您必须比较日期间隔以获得其间的所有日期。

首先，获取当天的开始和结束日期（为了简化，我们将基于结束日期在第二天开始，我们将在请求中排除它）：

  $ fromDate = new \ DateTime（'now'）; //例如2013-06-10 09:53:21 
 $ fromDate-> setTime（0，0，0）; //修改到2013-06-10 00:00:00，一天开始
 
 $ toDate = clone $ fromDate; 
 $ toDate-> modify（'+ 1天'）; //有2013-06-11 00:00:00 
  

并修改您的方法：

  public function findExpensesForDate（$ user，$ fromDate，$ toDate）
 {
 $ q = $ this 
  - > createQueryBuilder（'e'）
  - > where（'e.date> =：fromDate'）
  - > andWhere（'e.date<：toDate' 
  - > andWhere（'e.user =：user'）
  - > setParameter（'fromDate'，$ fromDate）
  - > setParameter（'toDate'，$ toDate） 
  - > setParameter（'user'，$ user）
  - > getQuery（）; 
 
 return $ q-> getResult（）; 
} 
  

这就是全部，应该工作，这里是脚本：

  $ expenses_for_today = $ this-> repository-> findExpensesForDate（$ this-> user，$ fromDate，$ toDate）; 
  

所以您将获得 2013-06-10 00:00之间的所有日期： 00 和 2013-06-11 00:00:00 （排除），所以当天的结果！

I'm using Symfony2 and Doctrine.

I have a date field, here it is:

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

In my form I use a text field to avoid Chrome default datepicker, but I insert DateTime objects in the database:

if ($request->isMethod('POST')) {
        $form->bind($request);

        //Convert string date to DateTime object and send it to database as object
        $dateObj = \DateTime::createfromformat('d-m-Y', $expense->getDate());
        $expense->setDate($dateObj);
        // ...

and then I want to find all items with a specific date:

public function findExpensesForDate($user, $date)
{
    $q = $this
        ->createQueryBuilder('e')
        ->where('e.date = :date')
        ->andWhere('e.user = :user')
        ->setParameter('date', $date)
        ->setParameter('user', $user)
         ->getQuery();

    return $q->getResult();
}

and call it like this:

$expenses_for_today = $this->repository->findExpensesForDate($this->user, $today);

which returns nothing when

$today = new /DateTime();

and returns the results when

$today_obj = new /DateTime();
$today = $today_obj->format('Y-m-d'); 

So why when I give the date as object this doesn't work? Isn't the reason to use date filed is to take advantage of quering with DateTime objects? I guess I'm missing something trivial and important, but I just can't see what, or I'm not understanding the situation quite well. My understanding is like this: the field is of type date, so I should insert DateTime objects in it and when quering I should also you DateTime objects. Can you please help me to fix this?

P.S.: I tried changing the field to datetime:

 /**
  * @ORM\Column(type="datetime")
  */
 protected $date;

but there was no change.

And at all is it OK and good to query with string? Will I get the advantage of using objects when querying that way?

解决方案

I think this is because a DateTime object is too much specific and have also hours, minutes and seconds, so it can't be equal to a registered date in database !

If you want to use DateTime objects, you need to get the date of the beginning of the day and the end date to get all results of the day ! You must compare an interval of dates to get all dates between it.

First, get the start and the end dates of the current day (to simplify, we will base the end date on the beginning of the next day, and we will exclude it in the request) :

$fromDate = new \DateTime('now'); // Have for example 2013-06-10 09:53:21
$fromDate->setTime(0, 0, 0); // Modify to 2013-06-10 00:00:00, beginning of the day

$toDate = clone $fromDate;
$toDate->modify('+1 day'); // Have 2013-06-11 00:00:00

And modify your method :

public function findExpensesForDate($user, $fromDate, $toDate)
{
    $q = $this
        ->createQueryBuilder('e')
        ->where('e.date >= :fromDate')
        ->andWhere('e.date < :toDate')
        ->andWhere('e.user = :user')
        ->setParameter('fromDate', $fromDate)
        ->setParameter('toDate', $toDate)
        ->setParameter('user', $user)
         ->getQuery();

    return $q->getResult();
}

That's all, it should work, here the script :

$expenses_for_today = $this->repository->findExpensesForDate($this->user, $fromDate, $toDate);

So you will get all the dates between the 2013-06-10 00:00:00 and the 2013-06-11 00:00:00 (excluded), so the results of the day !

这篇关于Doctrine的日期字段 - 如何编写查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！