CODEWITHSUNDEEP
symfonydqldoctrine-query
Robert
Robert

Reputation: 3483

Symfony2 Doctrine query builder use date function

how to use datetime functions in doctrine query builder,the below sql query need convert doctrine query(DQL).

SELECT SEC_TO_TIME((SUM(TIMESTAMPDIFF(MINUTE, StartTime, EndTime)) + COUNT(*)) * 60) as hours FROM table_name

The below one i have tried 

$qb = $emClient->createQueryBuilder()->select('SEC_TO_TIME((SUM(TIMESTAMPDIFF(MINUTE, en.startTime, swenendTime)) + COUNT(*)) * 60) as hours')
            ->from('AppBundle:MyEntity', 'en')
$result = $qb->getQuery()->getArrayResult();

But i got error undefined keyword at column 0,7

Thanks in advance

Upvotes: 1

Views: 1144

Answers (2)

Robert
Robert

Reputation: 20286

In order to use these function either you need to implement them on your own if they don't exist as @Denis Alimov suggested or you can use already implemented function with Doctrine Extensions Bundle. The last option is get all the data needed and perform some operations on PHP side. You can also execute PURE sql and map it to doctrine with ResultSetMappingBuilder

Upvotes: 1

Denis Alimov
Denis Alimov

Reputation: 2901

Doctrine supports following built-in functions http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/reference/dql-doctrine-query-language.html#functions-operators-aggregates

So either you implement your own functions or use plain SQL query $emClient->getConnection()->executeQuery()

Upvotes: 3

Related Questions