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'")
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?Answer1:
Considering you have a <em>DATETIME</em> field in you database, it is probably that using <em>LIKE</em> (this completely depends on the database and table's engine) you are not using algorithms that are optimized to DATE operations.
One solution is to specify the whole day (from the first to the last second of it) using the <strong>BETWEEN</strong> operator that is supported by <a href="http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/reference/dql-doctrine-query-language.html#id2" rel="nofollow">DQL</a> 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'")
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 <strong>DATE</strong> that does what you expect (it could easily do the before mentioned example above) using a <a href="http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/cookbook/dql-user-defined-functions.html" rel="nofollow">DQL User defined function</a>.