I want to perform a query which would look like this in native SQL:
SELECT
AVG(t.column) AS average_value
FROM
table t
WHERE
YEAR(t.timestamp) = 2013 AND
MONTH(t.timestamp) = 09 AND
DAY(t.timestamp) = 16 AND
t.somethingelse LIKE 'somethingelse'
GROUP BY
t.somethingelse;
If I am trying to implement this in Doctrine's query builder like this:
$qb = $this->getDoctrine()->createQueryBuilder();
$qb->select('e.column AS average_value')
->from('MyBundle:MyEntity', 'e')
->where('YEAR(e.timestamp) = 2013')
->andWhere('MONTH(e.timestamp) = 09')
->andWhere('DAY(e.timestamp) = 16')
->andWhere('u.somethingelse LIKE somethingelse')
->groupBy('somethingelse');
I get the error exception
[Syntax Error] line 0, col 63: Error: Expected known function, got 'YEAR'
How can I implement my query with Doctrines query builder?
Notes:
YEAR()
etc. statements, e.g. as seen here. But I am looking for a way to avoid including third party plugins.You can add Doctrine extension so you can use the MySql YEAR
and MONTH
statement by adding this configuration if you're on Symfony:
doctrine:
orm:
dql:
string_functions:
MONTH: DoctrineExtensions\Query\Mysql\Month
YEAR: DoctrineExtensions\Query\Mysql\Year
now you can use the MONTH and YEAR statements in your DQL or querybuilder.
Note: The extension supports MySQL, Oracle, PostgreSQL and SQLite.