Doctrine / SQL query MAX with GroupBy

Question

My goal is to select the column with the highest createdAt, grouped by a user.

If i do the following:

$qb = $this->createQueryBuilder('qb1');
$q = $qb
  ->select('pbs, MAX(pbs.createdAt) AS HIDDEN pbs_created')
  ->add('from', 'MyEntity pbs')
  ->groupBy('pbs.user')
  ->orderBy('pbs_created', 'ASC')
  ->getQuery();
return $q->getResult();

I get:

+------+---------------------+---------------------+--------------+
| id_0 | createdAt_3         | sclr_5              | project_id_7 |
+------+---------------------+---------------------+--------------+
| 2137 | 2014-10-07 10:52:29 | 2017-04-25 15:42:42 |          116 |
+------+---------------------+---------------------+--------------+
+------+---------------------+---------------------+--------------+
| 5123 | 2015-11-02 15:02:55 | 2017-05-02 05:36:18 |          118 |
+------+---------------------+---------------------+--------------+

The Problem is that i get the max value but the returned row contains not the value from max.

How can i get the newest createdAt grouped by a user with associated entity data?

score 2 · Answer 1 · answered Jun 13 '17 at 07:10

2

It has nothing todo with ordering. MAX() gives you the maximal value but not the row with maximal value

Look at these SO questions

The problem is - both solutions are pretty hard to achieve with Doctine's QueryBuilder. Subqueries are possible though - http://melikedev.com/2013/06/07/php-doctrine-dql-select-subquery/

answered Jun 13 '17 at 07:10

V-Light

3,065
3
25
31

I already thought someting like this and built a native query with your suggested solutions ( sub select ). – develth Jun 13 '17 at 09:11
Wondering why DQL cannot handle a sub select. – develth Jun 13 '17 at 09:11
Link isn't working anymore – Mathias Bader Mar 14 '22 at 06:18

score 0 · Answer 2 · answered Jun 13 '17 at 06:58

0

Have you tried to change the orderBy? Like this:

->orderBy('pbs_created', 'DESC')

answered Jun 13 '17 at 06:58

Alessandro Minoccheri

35,521
22
122
171

1

Yes. the orderBy will just affect after group by, so it will just sort your "old" or "not-wanted" entries. – develth Jun 13 '17 at 09:10

Doctrine / SQL query MAX with GroupBy

2 Answers2