CODEWITHSUNDEEP
phpmongodbaggregation-framework
Justin
Justin

Reputation: 45360

MongoDB aggregate with PHP - group by on date

I am using an aggregate in MongoDB with PHP. The code looks like:

$results = $c->aggregate(array(
    array(
      '$project' => array(
          'day' => array('$dayOfYear' => '$executed')
      ),
    ),
    array(
      '$group' => array(
          '_id' => array('day' => '$day'),
          'count' => array('$sum' => 1)
      ),
    ),
    array(
      '$sort' => array(
          '_id' => 1
      ),
    ),
    array(
      '$limit' => 30
    )
));

The problem with this, is that $dayOfYear does not sort correctly, because it sorts 2 then 3 then 345, 346... I need it to be date ascending. So, basically instead of simply doing $dayOfYear I need something like $year-$month-$dayOfMonth.

Unfortunately this does not work. Any ideas?

Thanks.

Upvotes: 4

Views: 3360

Answers (2)

Sammaye
Sammaye

Reputation: 43884

You can project those parts out and then group on them to enable you to group on the whole date:

$results = $c->aggregate(array(
    array(
      '$project' => array(
          'year' => array('$year' => '$executed' ),
          'month' => array('$month' => '$executed' ),
          'day' => array('$dayOfMonth' => '$executed')
      ),
    ),
    array(
      '$group' => array(
          '_id' => array('year' => '$year', 'month' => '$month', 'day' => '$day'),
          'count' => array('$sum' => 1)
      ),
    ),
    array(
      '$sort' => array(
          '_id.year' => 1,
          '_id.month' => 1,
          '_id.day' => 1
      ),
    ),
    array(
      '$limit' => 30
    )
));

Something like that should do the trick allowing you to sort on, as you stated: $year-$month-$dayOfMonth.

Upvotes: 3

JohnnyHK
JohnnyHK

Reputation: 312035

You shouldn't sort on the entire _id because you've set that up to contain an object (which can sort oddly), so change your $sort to this instead to specifically sort by day of year:

  '$sort' => array(
      '_id.day' => 1
  ),

Upvotes: 0

Related Questions