42

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:

  • I know about Doctrine's Native SQL. I've tried this, but it leads to the problem that my productive and my development database tables have different names. I want to work database agnostic, so this is no option.
  • Although I want to work db agnostic: FYI, I am using MySQL.
  • There is way to extend Doctrine to "learn" the YEAR() etc. statements, e.g. as seen here. But I am looking for a way to avoid including third party plugins.
Improve this question
0

5 Answers 5

Reset to default
62

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

Please notice the third point of my notes list. I know about this extension. But I am asking for a way to avoid this.
Sorry for that, actually I don't think there is any other way
Your comment is kind of the answer I was looking for. So I accepted this answer :-)
What if database is sqlite or pgsql?
@pikachu0 Editing an answer is more helpful for others than just leaving a comment when correcting an obviously wrong information like here.
|
15

In Symfony 4 you must install DoctrineExtensions:

composer require beberlei/DoctrineExtensions

And then edit the doctrine config file (config/packages/doctrine.yaml) as follow:

doctrine: orm: dql: string_functions: MONTH: DoctrineExtensions\Query\Mysql\Month YEAR: DoctrineExtensions\Query\Mysql\Year
Improve this answer

3 Comments

The question was not about Symfony4
It helps me a lot searching about DAY(). Even if it is not for Symfony 4, for me helps me for Symfony 4
@drAlberT Indeed, considering date of the post. But this anszer is kind of an update for people landing here now, so it is appreciable
4

For Symfony 4:

  1. Install: composer require beberlei/doctrineextensions
  2. Edit: config\packages\doctrine.yaml
 doctrine: orm: dql: datetime_functions: DAY: DoctrineExtensions\Query\Mysql\Day MONTH: DoctrineExtensions\Query\Mysql\Month YEAR: DoctrineExtensions\Query\Mysql\Year
  1. Edit your controller:
 public function somex() { $em = $this->getDoctrine()->getManager(); $emConfig = $em->getConfiguration(); $emConfig->addCustomDatetimeFunction('YEAR', 'DoctrineExtensions\Query\Mysql\Year'); $emConfig->addCustomDatetimeFunction('MONTH', 'DoctrineExtensions\Query\Mysql\Month'); $emConfig->addCustomDatetimeFunction('DAY', 'DoctrineExtensions\Query\Mysql\Day'); $day = '22'; $month = '4'; $qb = $em->createQueryBuilder() ->select('u') ->from('App\Entity\User', 'u') ->where('DAY(u.somedate) = :day') ->andwhere('MONTH(u.somedate) = :month') ->setParameter('month', $day) ->setParameter('day', $month) ; $trab = $qb->getQuery()->getResult(); return $this->render('intranet/somex.html.twig', [ 'trab' => $trab ]); } ````
Improve this answer

Comments

3

orocrm/doctrine-extensions seems to be a good project too

It supports both MySQL and PostgreSql .. the goal is to be cross DB

Improve this answer

Comments

1

go to this page and choose your database systeme and pick up functions thats you want

... I too had the same problem as you... then I modified my doctrine.yaml file using exactly the same names as in the github file and it worked.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.