Doctrine ODM allows to group a set of matched documents: https://www.doctrine-project.org/projects/doctrine-mongodb-odm/en/2.9/reference/aggregation-stage-reference.html#group
Consider documents having properties $type
and $date
. I'd like to get data grouped by type and sub-grouped by month and year.
So far, I was able to group like the following:
array(2) {
["_id"]=> array(1) {
["type"]=> string(6) "A"
}
["byMonth"]=> array(5) {
[0]=> array(3) {
["month"]=> int(7) ["year"]=> int(2025) ["count"]=> int(1)
}
[1]=> array(3) {
["month"]=> int(7) ["year"]=> int(2025) ["count"]=> int(1)
}
[2]=> array(3) {
["month"]=> int(3) ["year"]=> int(2025) ["count"]=> int(1)
}
[3]=> array(3) {
["month"]=> int(3) ["year"]=> int(2025) ["count"]=> int(1)
}
[4]=> array(3) {
["month"]=> int(1) ["year"]=> int(2025) ["count"]=> int(1)
}
}
}
array(2) {
["_id"]=> array(1) {
["type"]=> string(6) "B"
}
["byMonth"]=> array(1) {
[0]=> array(3) {
["month"]=> int(2) ["year"]=> int(2025) ["count"]=> int(1)
}
}
}
But as you can see, data is not aggregated for type A on months 3 and 7 (notice the count
field).
Here is the code used:
$aggBuilder = $dm->createAggregationBuilder(Example::class);
$aggBuilder->match()
->field('date')
->gte($from)
->lte($to)
;
$aggBuilder->group()
->field('id')
->expression(
$aggBuilder->expr()
->field('type')
->expression('$type')
)
->field('byMonth')
->push(
$aggBuilder->expr()
->field('month')
->month('$date')
->field('year')
->year('$date')
)
;
Instead of just pushing the data, how would you do to aggregate the nested data by month like below?
array(2) {
["_id"]=> array(1) {
["type"]=> string(6) "A"
}
["byMonth"]=> array(3) {
[0]=> array(3) {
["month"]=> int(7) ["year"]=> int(2025) ["count"]=> int(2)
}
[1]=> array(3) {
["month"]=> int(3) ["year"]=> int(2025) ["count"]=> int(2)
}
[2]=> array(3) {
["month"]=> int(1) ["year"]=> int(2025) ["count"]=> int(1)
}
}
}
array(2) {
["_id"]=> array(1) {
["type"]=> string(6) "B"
}
["byMonth"]=> array(1) {
[0]=> array(3) {
["month"]=> int(2) ["year"]=> int(2025) ["count"]=> int(1)
}
}
}
Credit for the answer goes to alcaeus who is part of the contributors of the Doctrine MongoDB ODM project.
You’ll have to use
$group
twice: first to get a count for each group of type, month, and year. Then a second$group
to get one document for each type that has the previous groups in an array. Here’s some sample code that I haven’t tested:$builder ->group() ->field('_id') ->expression( $builder->expr() ->field('type') ->expression('$type') ->field('month') ->month('$date') ->field('year') ->year('$date') ) ->field('count') ->sum(1) ->group() ->field('_id') ->expression('$_id.type') ->field('byMonth') ->push( $builder->expr() ->field('month') ->expression('$_id.month') ->field('year') ->expression('$_id.year') ->field('count') ->expression('$count') ) ;