Pada MySQL menjalankan query group by dan count atau sum
select kolomA, count(kode) as jumkode from tabel where status='oke' group by kolomA order by jumkode DESC
Pada mongoDB, menggunakan fungsi aggregate
db.namacollection.aggregate([{ $match: { status: "oke" } },{"$group": {_id: "$kolomA",count: {$sum: 1}}},{$sort: {count: -1}}])
Menggunakan php driver mongoDB :
$manager = new MongoDB\Driver\Manager();
$command = new MongoDB\Driver\Command([
'aggregate' => 'namacollection',
'pipeline' => [
['$match'=>['status'=>'oke']],
['$group' => ['_id' => '$kolomA', 'count' => ['$sum' => 1]]],
['$sort'=>['count'=>-1]],
],
'cursor' => new stdClass,
]);
$cursor = $manager->executeCommand('namadatabase', $command);
Memberi filter range tanggal dari tanggal sekian sampai tanggal sekian :
db.namacollection.aggregate([{ $match: { tanggal: {$gte:ISODate("2021-05-01"),$lt:ISODate("2020-05-30"}} } },{"$group": {_id: "$kolomA",count: {$sum: 1}}},{$sort: {count: -1}}])
//$gte bisa diganti $gt kalau error
Versi php adalah :
$manager = new MongoDB\Driver\Manager();
$command = new MongoDB\Driver\Command([
'aggregate' => 'namacollection',
'pipeline' => [
['$match'=>['tanggal'=>
[
'$gt'=>new MongoDB\BSON\UTCDatetime(strtotime('2021-05-01 00:00:00') * 1000),
'$lt'=>new MongoDB\BSON\UTCDatetime(strtotime('2020-05-30 23:59:59') * 1000),
]
]
],
['$group' => ['_id' => '$kolomA', 'count' => ['$sum' => 1]]],
['$sort'=>['count'=>-1]],
],
'cursor' => new stdClass,
]);
$cursor = $manager->executeCommand('namadatabase', $command);
//format kolom tanggal haruslah berupa datetime
