Doctrine 2 - 通过datetime字段的日期部分过滤结果 [英] Doctrine 2 - Filter results by a datetime field's date part

问题描述

我有一个名为 Szerzodes 的实体实体，日期时间字段称为 exportalva 。获取 exportalva 字段的日期部分是给定值的记录是否可能？例如，我想获得在某一天出口的记录，不管时间如何。我试过

I have a doctrine entity called Szerzodes with a datetime field called exportalva. Is it possible somehow to get the records of which the exportalva field's date part is a given value? E.g I want to get the records that were exported on a given day, regardless of the time. I've tried to

$em->createQuery("SELECT sz FROM Szerzodes sz WHERE DATE(sz.exportalva) = '2012-05-17'")

但由于DATE（）不是已知函数用于DQL。现在我暂时使用

but it failed as DATE() is not a known function for DQL. Now I temporarily use

$em->createQuery("SELECT sz FROM Szerzodes sz WHERE sz.exportalva LIKE '2012-05-17%'")

但这似乎是一个丑陋的黑客。有没有更好的方法呢？

but it seems to be an ugly hack for me. Is there a better way to do this?

推荐答案

考虑到你在数据库中有一个 DATETIME 字段，这可能是使用 LIKE （这完全取决于数据库和表的引擎），您没有使用针对DATE操作进行优化的算法。

Considering you have a DATETIME field in you database, it is probably that using LIKE (this completely depends on the database and table's engine) you are not using algorithms that are optimized to DATE operations.

一个解决方案是使用 BETWEEN 运算符来指定整个一天（从第一个到最后一个） .org / projects / doctrine-orm / en / latest / reference / dql-doctrine-query-language.html＃id2rel =nofollow> DQL ，如EBNF所示：

One solution is to specify the whole day (from the first to the last second of it) using the BETWEEN operator that is supported by DQL as seen in it's EBNF:

$em->createQuery("SELECT sz FROM Szerzodes sz WHERE sz.exportalva BETWEEN '2012-05-17 00:00:00' AND '2012-05-17 23:59:59'")

如果你真的想使用一个函数因此，在值中始终使用函数，而不是条件中使用的列也是一个好主意。一些数据库（如MySQL）在本机函数修改的列中不使用索引。您可以使用 DATE 的用户函数（可以轻松完成上述示例） project / doctrine-orm / en / latest / cookbook / dql-user-defined-functions.htmlrel =nofollow> DQL用户定义的功能。

If you really want to use a function for that, it is also a good idea to use a function always in the value and not the column used in the condition. Some databases (like MySQL) do not use indexes in columns modified by native functions. You can create a user function called DATE that does what you expect (it could easily do the before mentioned example above) using a DQL User defined function.

这篇关于Doctrine 2 - 通过datetime字段的日期部分过滤结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！