Query birthdays of the day with Doctrine ORM

Question

With raw SQL I habitually do the following:

SELECT * FROM users WHERE MONTH(birthday) = 11 AND DAY(birthday) = 17.

But these MONTH() and DAY() functions does not work with all databases =/

So what is the best way to do this that works with any database supported by Doctrine?

Answer 1

To make this calcuation at SQL server level, you will have to create some custom DQL functions:

http://www.doctrine-project.org/blog/doctrine2-custom-dql-udfs.html

Take a look at official doc too:

http://doctrine-orm.readthedocs.org/en/latest/reference/dql-doctrine-query-language.html#adding-your-own-functions-to-the-dql-language

Here is an example with postgresql DISTANCE functions:

https://github.com/KnpLabs/DoctrineBehaviors/blob/master/src/Knp/DoctrineBehaviors/ORM/Geocodable/Query/AST/Functions/DistanceFunction.php

Your concern is to make this work for ANY suuported RDBM ? Take advantage of DatabasePlatform in your getSql() method:

<?php

public function getSql(SqlWalker $walker)
{
    $walker->getConnection()->getDatabasePlatform(); // platform specific stuff
}