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!

Genti Saliu · Accepted Answer · 2016-01-18T14:37:44.253

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();

edited Jan 18 '16 at 14:37

answered Jan 18 '16 at 11:19

Genti Saliu

2,643
4
23
43

no, not really. I want to load all users that have bdays in 7 days. – Tiberiu Jan 18 '16 at 11:27
1

Changed my answer to load users who have birthday in exactly 7 days. – Genti Saliu Jan 18 '16 at 14:08
The query builder answer worked! Genti, you are a saver, thank you! – Tiberiu Jan 18 '16 at 14:59

SQL query convert to DQL (Doctrine for Symfony2)

1 Answers1