How can I use SQL's YEAR(), MONTH() and DAY() in Doctrine2?

Question

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.

score 60 · Accepted Answer · edited May 17 '20 at 20:43

60

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.

edited May 17 '20 at 20:43

Nuryagdy Mustapayev

667
1
7
27

answered Oct 16 '13 at 10:09

alex88

4,788
4
39
60

1

Please notice the third point of my notes list. I know about this extension. But I am asking for a way to avoid this. – Gottlieb Notschnabel Oct 16 '13 at 13:31
Sorry for that, actually I don't think there is any other way – alex88 Oct 16 '13 at 15:29
Yeah, I suspected that. Thanks anyway! – Gottlieb Notschnabel Oct 16 '13 at 15:40
2

Your comment is kind of the answer I was looking for. So I accepted this answer :-) – Gottlieb Notschnabel Nov 07 '13 at 15:39
3

What if database is sqlite or pgsql? – sensorario Feb 14 '14 at 11:43
2

@pikachu0 Editing an answer is more helpful for others than just leaving a comment when correcting an obviously wrong information like here. – fishbone Jul 03 '17 at 13:33
@fishbone by the way, reviewing [your suggested edit](https://stackoverflow.com/review/suggested-edits/16597308) I see you missed to see the 2nd line. It is within the parentheses, in _[this][2] if you're using Symfony_ – fedorqui Jul 03 '17 at 14:38
Why do you think i missed the 2nd line? I removed the Symfony bundle link on purpose, because there is no bundle for the correct extension, however there was a bundle for the wrong extension. – fishbone Jul 03 '17 at 16:16

score 15 · Answer 2 · edited Aug 08 '19 at 04:20

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

edited Aug 08 '19 at 04:20

Artyum

103
2
8

answered Jun 02 '19 at 21:38

betonowelasy

161
1
3

The question was not about Symfony4 – drAlberT Aug 26 '19 at 17:32
2

It helps me a lot searching about DAY(). Even if it is not for Symfony 4, for me helps me for Symfony 4 – shakaran Sep 27 '19 at 10:28
3

@drAlberT Indeed, considering date of the post. But this anszer is kind of an update for people landing here now, so it is appreciable – Erdal G. Jul 20 '20 at 15:11

score 3 · Answer 3 · answered Aug 05 '15 at 13:33

3

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

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

answered Aug 05 '15 at 13:33

drAlberT

22,059
5
34
40

score 3 · Answer 4 · answered Oct 17 '19 at 16:45

For Symfony 4:

Install: composer require beberlei/doctrineextensions
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

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
        ]);
    }
    ````

score 1 · Answer 5 · answered Feb 20 '20 at 01:06

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.

answered Feb 20 '20 at 01:06

babysure

61
1
7

How can I use SQL's YEAR(), MONTH() and DAY() in Doctrine2?

5 Answers5

Linked

Related