0
I would like to format a date with Doctrine, often I save a date like datetime in the database but want to perform a search grouping per day without considering minutes and seconds. Does anyone know a cool way to do that ?
1 answer
1
Doctrine does not yet have the full range of SQL functions, they are working on it for future versions according to Cookbook. Until then you have to create the missing functions, in my case create the DATE function
namespace ByteinCoffee\ExtraBundle\Doctrine\DQL;
use Doctrine\ORM\Query\AST\Functions\FunctionNode;
use Doctrine\ORM\Query\Lexer;
use Doctrine\ORM\Query\Parser;
use Doctrine\ORM\Query\SqlWalker;
/**
* @author Fábio Lemos Elizandro <[email protected]>
*
* DateFunction ::= "DATE" "(" ArithmeticPrimary ")"
*/
class Date extends FunctionNode
{
public $dateExpression = null;
public function parse(Parser $parser)
{
$parser->match(Lexer::T_IDENTIFIER);
$parser->match(Lexer::T_OPEN_PARENTHESIS);
$this->dateExpression = $parser->ArithmeticPrimary();
$parser->match(Lexer::T_CLOSE_PARENTHESIS);
}
public function getSql(SqlWalker $sqlWalker)
{
return \sprintf('DATE(%s)', $this->dateExpression->dispatch($sqlWalker));
}
}
to register the function
doctrine:
#......
orm:
dql:
datetime_functions:
date: ByteinCoffee\ExtraBundle\Doctrine\DQL\Date
If anyone knows a Bundle who does this leave as well as reply. Thank you
Browser other questions tagged php symfony-2 doctrine-2
You are not signed in. Login or sign up in order to post.
To do this with common SQL is very easy and with DQL would also be - I believe it is just to do a
GROUP BY DATE_FORMAT(data, "%Y-%m-%d"). Have you tried?– Rodrigo Rigotti
I have tried, as far as I know there is no DATE_FORMAT for Doctrine, I came here hoping to discover some Bundle that has created this DQL function
– Fábio Lemos Elizandro
What kind of consultation are you trying to make? Normally we use DQL when we want to translate the resultset of a query into the application entities, but how you want to bring a grouped result (because of the
GROUP BY), it would be more interesting to run a native SQL (through Doctrine itself).– Rodrigo Rigotti
In fact DQL is a query language for your object model, namely for you not to keep writing table names or have to worry about your data model. The result set is independent of being DQL or SQL, I didn’t want to have to write a native query =(. I was thinking of leaving two fields in my entity a date and another team, but I don’t know if this is valid
– Fábio Lemos Elizandro
Actually when you run a DQL, the resultset is an object (or set of objects) of your application. When you run an SQL, the resultset is an array. Personally, I see no problem in using the two techniques, which for me would be more complementary than conflicting. It seems to me that your problem is solved with an SQL even. :)
– Rodrigo Rigotti
Okay, thank you very much.
– Fábio Lemos Elizandro