Symfony2 Doctrine query builder use date function

Question

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

Denis Alimov · Accepted Answer · 2018-03-13 07:15:16Z

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()

Robert · Accepted Answer · 2018-03-13 09:32:20Z

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

Collectives™ on Stack Overflow

Symfony2 Doctrine query builder use date function

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related