Doctrine DQL query with SQL functions

Question

I'm porting simple web application written in CodeIgniter to Symfony2 bundle. I'm new into Symfony2 and Doctrine and I've a problem with one SQL query, that I want to rewrite in DQL. I've all ready to go in my bundle, I've created Entity class and I'm able to insert data to database and do simple queries in Object-Oriented-Programming 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(t.x,3) = ".round($x, 3);

As you can see there are some SQL function calls, that needs to be executed on database server. Doctrine can't understand this calls. For sure, I have an option to quit using Doctrine and do this query using basic PDO inside my Symfony2 bundle, but I would like to take full advantages of using Symfony2 and Doctrine. So I would like to have this done in OOP way or using clever DQL query that understands something like:

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

but being able to rewrite my SQL query from old application to my new bundle is a must. Please, help me finding right solution.

Boatswain

Answer 1

I know I meets a little late but if this allows the other to find an alternative solution :

use bundle : Doctrine Extensions

after configure your config.yml :

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

Friede

Answer 2

Unfortunately you can't use SQL functions in DQL query. So you have 2 options:

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

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

Judging by the date on the question I don't think this can help you now but I hope it can help others that have the same problem

Alfilaria