phpaggregation-frameworkdoctrine-odm

Nested grouping with Doctrine ODM MongoDB aggregation framework


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)
    }
  }
}

Solution

  • 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')
        )
      ;