Doctrine DQL Query with SQL Functions

Question

I am porting a simple web application written in CodeIgniter to the Symfony2 package. I am new to Symfony2 and Doctrine, and I have a problem with a single SQL query that I want to rewrite in DQL. I'm ready to go into my package, I created an Entity class, and I can insert data into the database and make simple queries in Object-Oriented-Programming in the way that Symfony2 provides. Unfortunately, I have no idea how to implement this SQL query in DQL:

$sql = "SELECT * FROM t WHERE UNIX_TIMESTAMP(t.date) > ".(time()-300)." AND ROUND(tx,3) = ".round($x, 3); 

As you can see, there are some SQL function calls that must be executed on the database server. The doctrine cannot understand these calls. Of course, I have the opportunity to exit Doctrine and make this request using the underlying PDO inside my Symfony2 package, but I would like to take full advantage of using Symfony2 and Doctrine. Therefore, I would like this to be done in OOP mode or using a smart DQL query that understands something like:

 $em->createQuery("SELECT t FROM MyTestBundle:MyEntity t WHERE tx = :x") ->setParameter("x", round($x,3)); 

but the ability to rewrite my SQL query from the old application to my new package is a must. Please help me find the right solution.

Answer 1

I know that I meet a little late, but if this allows another to find an alternative solution:

use bundle: Doctrine extensions

after setting config.yml:

 doctrine: orm: dql: string_functions: UNIX_TIMESTAMP: DoctrineExtensions\Query\Mysql\UnixTimestamp 

Answer 2

Unfortunately, you cannot use SQL functions in a DQL query. So you have 2 options:

• You can create a custom function in DQL. Here is the official Doctrine documentation on how to do this http://docs.doctrine-project.org/en/latest/cookbook/dql-user-defined-functions.html

• Or you can execute the SQL query directly in the doctrine. Here is the official documentation for using Native SQL in Doctrine http://docs.doctrine-project.org/en/latest/reference/native-sql.html

Judging by the date in the question, I don’t think it can help you now, but I hope it can help others who have the same problem