SQL query convert to DQL (Doctrine for Symfony2)

Question

How can I use this query in DQL (Symfony2):

SELECT id FROM user WHERE DATE_FORMAT( birth_date ,'%m-%d') = DATE_FORMAT( NOW() + INTERVAL 7 DAY ,'%m-%d')

I use beberlei/DoctrineExtensions, I added this is my config:

orm:
dql:
    string_functions:
        date_format: DoctrineExtensions\Query\Mysql\DateFormat
        dateadd: DoctrineExtensions\Query\Mysql\DateAdd

I tried this, inside the UserRepository:

$qb = $this->createQueryBuilder('U');
$qb ->select('U')
    ->where( 'DATE_FORMAT( U.birthDate, %m-%d) = DATE_FORMAT( CURRENT_TIMESTAMP() + INTERVAL 7 DAY, %m-%d' );

but it's not working.

Thanks in advance!

Answer 1

To load all users whose birthday is in 7 days:

// Using DQL
$dql = "SELECT u FROM user u WHERE DATE_FORMAT(u.birthDate, '%m-%d') = DATE_FORMAT(:dateIn7Days, '%m-%d')";

$query = $em->createQuery($dql);
$query->setParameter('dateIn7Days', new \DateTime('+7 days'));

$users = $query->getResult();

With QueryBuilder:

$qb = $this->createQueryBuilder();

$qb
   ->select('u')
   ->from('User', 'u')
   ->where(
       $qb->expr()->eq(
           "DATE_FORMAT(u.birthDate, '%m-%d')",
           "DATE_FORMAT(:dateIn7Days, '%m-%d')")
       )
   ->setParameter('dateIn7Days', new \DateTime('+7 days'));

$query = $qb->getQuery();
$users = $query->getResult();