Geek Answers Handbook

MongoDB integrates with PHP - group by date

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

 $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 the date to be upstream. So basically instead of just executing $dayOfYear I need something like $year-$month-$dayOfMonth .

Unfortunately this will not work. Any ideas?

Thanks.

+4
source share
2 answers

You can project these parts and then group them so you can group the entire 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 this should do the trick, allowing you to sort, as you stated: $year-$month-$dayOfMonth .

+3
source

You cannot sort by the whole _id tag because you set it to accommodate an object (which can be sorted oddly), so instead change $sort to it to sort by date of year:

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

More articles:


All Articles