SELECT entity with multiple GROUP BY returns partial result #5698

New Issue

admin · 2026-01-22T15:14:53+01:00

admin commented

2026-01-22 15:14:53 +01:00

Originally created by @BenMorel on GitHub (Sep 12, 2017).

I have 3 entities:

┌────────────┐
│ Commission │
│────────────│
│         id │        ┌──────────────┐
│   merchant │ *───>1 │     Merchant │
│     amount │        │──────────────│
└────────────┘        │           id │        ┌─────────┐
                      │      network │ *───>1 │ Network │
                      │         name │        │─────────│
                      │ currencyCode │        │      id │
                      └──────────────┘        │    name │
                                              └─────────┘

I'm trying to select the total commission amounts, for each network, grouped by currency.
This translates into GROUP BY network, merchant.currencyCode.

If I select Network.id only, it works fine:

SELECT n.id AS networkId, m.currencyCode, SUM(c.amount) AS amount
FROM Model\Network n
INNER JOIN Model\Merchant m WITH m.network = n
INNER JOIN Model\Commission c WITH c.merchant = m
GROUP BY n, m.currencyCode
ORDER BY n.name, m.currencyCode

+---------+----------+--------+
| Network | Currency | Amount |
+---------+----------+--------+
|       1 | EUR      |    100 |
|       1 | USD      |    200 |
|       2 | EUR      |    400 |
|       2 | GBP      |    300 |
|       3 | CAD      |    600 |
|       3 | USD      |    500 |
+---------+----------+--------+

Now if I select the Network entity:

SELECT n AS network, m.currencyCode, SUM(c.amount) AS amount
...

It still works fine when using HYDRATE_ARRAY:

+---------+----------+--------+
| Network | Currency | Amount |
+---------+----------+--------+
|       1 | EUR      |    100 |
|       1 | USD      |    200 |
|       2 | EUR      |    400 |
|       2 | GBP      |    300 |
|       3 | CAD      |    600 |
|       3 | USD      |    500 |
+---------+----------+--------+

But when using HYDRATE_OBJECT, it only returns the last result for each Network:

+---------+----------+--------+
| Network | Currency | Amount |
+---------+----------+--------+
|       1 | USD      |    200 |
|       2 | GBP      |    300 |
|       3 | USD      |    500 |
+---------+----------+--------+

I created a reproducible example in this repository:
https://github.com/BenMorel/doctrine-groupBy-bug

I could not find any documentation for this behaviour, which I believe is a bug, as it behaves differently based on the hydration mode, in addition to being counter-intuitive.

Originally created by @BenMorel on GitHub (Sep 12, 2017). I have 3 entities: ┌────────────┐ │ Commission │ │────────────│ │ id │ ┌──────────────┐ │ merchant │ *───>1 │ Merchant │ │ amount │ │──────────────│ └────────────┘ │ id │ ┌─────────┐ │ network │ *───>1 │ Network │ │ name │ │─────────│ │ currencyCode │ │ id │ └──────────────┘ │ name │ └─────────┘ I'm trying to select the total commission amounts, for each network, grouped by currency. This translates into `GROUP BY network, merchant.currencyCode`. If I select `Network.id` only, it works fine: SELECT n.id AS networkId, m.currencyCode, SUM(c.amount) AS amount FROM Model\Network n INNER JOIN Model\Merchant m WITH m.network = n INNER JOIN Model\Commission c WITH c.merchant = m GROUP BY n, m.currencyCode ORDER BY n.name, m.currencyCode +---------+----------+--------+ | Network | Currency | Amount | +---------+----------+--------+ | 1 | EUR | 100 | | 1 | USD | 200 | | 2 | EUR | 400 | | 2 | GBP | 300 | | 3 | CAD | 600 | | 3 | USD | 500 | +---------+----------+--------+ Now if I select the `Network` entity: SELECT n AS network, m.currencyCode, SUM(c.amount) AS amount ... It still works fine when using `HYDRATE_ARRAY`: +---------+----------+--------+ | Network | Currency | Amount | +---------+----------+--------+ | 1 | EUR | 100 | | 1 | USD | 200 | | 2 | EUR | 400 | | 2 | GBP | 300 | | 3 | CAD | 600 | | 3 | USD | 500 | +---------+----------+--------+ But when using `HYDRATE_OBJECT`, it only returns the last result for each Network: +---------+----------+--------+ | Network | Currency | Amount | +---------+----------+--------+ | 1 | USD | 200 | | 2 | GBP | 300 | | 3 | USD | 500 | +---------+----------+--------+ I created a reproducible example in this repository: https://github.com/BenMorel/doctrine-groupBy-bug I could not find any documentation for this behaviour, which I believe is a bug, as it behaves differently based on the hydration mode, in addition to being counter-intuitive.

admin closed this issue

2026-01-22 15:14:54 +01:00

admin commented

2026-01-22 15:14:57 +01:00

@beberlei commented on GitHub (Sep 16, 2017):

This is the expected behaviour, the object hydration returns exactly one object of each type, in this case overwriting the mixed result group by values.

I just checked and you are right about this not being documented, so I have added it to the docs for the next person: 0c4f9a8866

@beberlei commented on GitHub (Sep 16, 2017): This is the expected behaviour, the object hydration returns exactly one object of each type, in this case overwriting the mixed result group by values. I just checked and you are right about this not being documented, so I have added it to the docs for the next person: https://github.com/doctrine/doctrine2/commit/0c4f9a886682441628f895bda38bdbb3c1703900

admin commented

2026-01-22 15:14:57 +01:00

@BenMorel commented on GitHub (Sep 16, 2017):

Interesting, thanks. Is there any technical reason why it behaves this way? Would it be possible to create another object hydrator that would behave as I expected?

At the moment I have to retrieve Network ids instead, and do a separate SELECT ... WHERE id in(...) query to load the Networks separately.

@BenMorel commented on GitHub (Sep 16, 2017): Interesting, thanks. Is there any technical reason why it behaves this way? Would it be possible to create another object hydrator that would behave as I expected? At the moment I have to retrieve Network ids instead, and do a separate `SELECT ... WHERE id in(...)` query to load the Networks separately.

Sign in to join this conversation.

Branches Tags

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: doctrine/archived-orm#5698