Group By day and month Doctrine

Question

I'd like to list my users by birthday, so month and day but not year.

I have this query

SELECT * 
FROM user 
WHERE birthDate IS NOT NULL 
GROUP BY MONTH(birthDate), DAY(birthDate)

But I don't know how to use it with Symfony and Doctrine. I tried

$result = $em->getRepository("AcmeApplicationBundle:SecurityServiceUser")
            ->createQueryBuilder('user')
            ->where('user.birthDate IS NOT NULL')
            ->groupBy('MONTH(user.birthDate), DAY(user.birthDate)')
            ->getQuery()
            ->getResult();

And

$result = $em->getRepository("AcmeApplicationBundle:SecurityServiceUser")
            ->createQueryBuilder('user')
            ->where('user.birthDate IS NOT NULL')
            ->groupBy('MONTH(user.birthDate)')
            ->groupBy('DAY(user.birthDate)')
            ->getQuery()
            ->getResult();

But in both cases I have an error

[Semantical Error] line 0, col 165 near 'MONTH(birthDate),': Error: Cannot group by undefined identification or result variable.

Maybe this one helps: http://stackoverflow.com/questions/17702674/groupby-day-using-doctrine2 — wumpz, Mar 16 '14 at 22:04
Did you install [https://github.com/beberlei/DoctrineExtensions](Doctrine Extensions) ? — Javad, Mar 17 '14 at 01:53

score 37 · Accepted Answer · edited Jan 11 '16 at 22:05

37

You haven't set an alias for your values. Here is an updated version :

   $result = $em->getRepository("AcmeApplicationBundle:SecurityServiceUser")
        ->createQueryBuilder('user')
        ->select(' user.username, MONTH(user.birthDate) AS gBmonth, DAY(user.birthDate) AS gBday')
        ->where('user.birthDate IS NOT NULL')
        ->groupBy('gBmonth')
        ->addGroupBy('gBday')
        ->getQuery()
        ->getResult();

This should work fine.

edited Jan 11 '16 at 22:05

Community

1
1

answered Mar 16 '14 at 23:01

Charles-Antoine Fournel

1,713
1
25
37

Why do I have to select it first before I can group it – simPod Aug 17 '16 at 13:21
1

Ils very simple, like in a classic sql query, you have to define first your alias in select in order to use it with group – Charles-Antoine Fournel Aug 17 '16 at 13:29
1

This throw me this error, why? `Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'my_database.o0_.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by` – elvismdev Mar 16 '19 at 19:27
You also need to install ´composer require beberlei/DoctrineExtensions´ – Jorgeeadan Sep 26 '19 at 22:15
@elvismdev, these links will likely help to understand: 1.) https://dev.mysql.com/doc/refman/8.0/en/group-by-handling.html. 2.) https://medium.com/@riccardoodone/the-love-hate-relationship-between-select-and-group-by-in-sql-4957b2a70229 – John Pancoast Mar 01 '20 at 03:58

score -1 · Answer 2 · edited Dec 29 '21 at 13:10

-1

You can use one of these:

format(as.Date,)

or

strftime(source, format)

edited Dec 29 '21 at 13:10

Tomerikoo

18,379
16
47
61

answered Dec 29 '21 at 13:01

Tevres

11

Group By day and month Doctrine

2 Answers2