I am recording golf scores and now trying to calculate the leaderboards. This is for use on a PHP site, so I also need to translate the pipeline code (afterwards!).
Each round document has a sub-array of holes:
{
"playerName": "Tiger Woods",
"comp": {
"name": "US Open 2021",
"courseId": "608952e3abebbd503ba6e115",
"scoringMethod": "Stableford",
"tees": "Blue",
"courseName": "MAGC Middle 9",
"roundNo": 2,
"id": {
"$oid": "607019361c071256e4f0d0d5"
}
},
"holes": [
{
"blue": 431,
"holeNett": 4,
"par": 4,
"holeGross": 5,
"no": 1,
"holeNettPoints": 2,
"yellow": 420,
"si": 2,
"holeStrokes": 1,
"white": 444,
"red": 399
},
{
"blue": 335,
"holeNett": 5,
"par": 4,
"holeGross": 5,
"no": 2,
"holeNettPoints": 1,
"yellow": 320,
"si": 8,
"holeStrokes": 0,
"white": 350,
"red": 295
},
{
"blue": 385,
"holeNett": 4,
"par": 4,
"holeGross": 5,
"no": 3,
"holeNettPoints": 2,
"yellow": 362,
"si": 4,
"holeStrokes": 1,
"white": 400,
"red": 343
},
{
"blue": 161,
"holeNett": 3,
"par": 3,
"holeGross": 3,
"no": 4,
"holeNettPoints": 2,
"yellow": 144,
"si": 7,
"holeStrokes": 0,
"white": 180,
"red": 105
},
{
"blue": 461,
"holeNett": 5,
"par": 5,
"holeGross": 6,
"no": 5,
"holeNettPoints": 2,
"yellow": 439,
"si": 1,
"holeStrokes": 1,
"white": 473,
"red": 418
},
{
"blue": 330,
"holeNett": 4,
"par": 4,
"holeGross": 4,
"no": 6,
"holeNettPoints": 2,
"yellow": 300,
"si": 9,
"holeStrokes": 0,
"white": 337,
"red": 281
},
{
"blue": 381,
"holeNett": 6,
"par": 4,
"holeGross": 6,
"no": 7,
"holeNettPoints": 0,
"yellow": 363,
"si": 5,
"holeStrokes": 0,
"white": 390,
"red": 290
},
{
"blue": 152,
"holeNett": 3,
"par": 3,
"holeGross": 3,
"no": 8,
"holeNettPoints": 2,
"yellow": 140,
"si": 6,
"holeStrokes": 0,
"white": 167,
"red": 131
},
{
"blue": 366,
"holeNett": 3,
"par": 4,
"holeGross": 4,
"no": 9,
"holeNettPoints": 3,
"yellow": 344,
"si": 3,
"holeStrokes": 1,
"white": 396,
"red": 327
}
],
"playerId": "609d0993906429612483cea0",
"holeCount": 9,
"countbackScores": [
16,
11,
5,
2
],
"team": "magc",
"date": "2021-05-20T23:00:00+00:00",
"computedScore": 16,
"computedThru": 9,
"_id": {
"$oid": "60a6aa828078924a6065dc9e"
}
}
I have an aggregation pipeline that finds the highest or lowest (competition rules dependent) score on every hole. I had to $unwind the sub-array, producing a pipeline document per hole played. Typically this means either 18 documents or 9 for a half-round, per player. Also to note, each round could be played on a different comp.courseId (there are 3 courses in my initial comp) - so these need to be grouped by as well, hence it is currently in my $match filter.
Can I in the same pipeline calculate the cumulative total of final pipeline output, at the same time as retaining the previous documents?
[{$match: {
"comp.id" : ObjectId('600019361c071256e4f0d0d5'),
"playerId" : "600d0993906429612483cea0",
"comp.courseId" : "600955aaabebbd503ba6e116"
}
},
{$unwind: {
path : "$holes"
}},
{$group: {
_id: "$holes.no",
hole: {
$max: "$holes"
}
}},
{$set: {
"total": { "$sum" : "$hole.holeNettPoints" }
}},
{$sort: {
"hole.no": 1
}
}]
The $set pipeline is the stage I am testing at. But this only sums the holeNettPoints field, from a single document, so is the same as this value in each document.
I don't think I can do another $group of all these documents, as on my PHP page though, I need the other per-hole fields to build an HTML table. But its sorted by the cumulative total for the round. So I have tried adding a field with $set. Is this correct?
This returns a document per hole played (9 with my current data, commonly 18 for a full round of golf). I would like to have the cumulative holeNettPoints total for all 9 holes as well as the $max per hole holeNettPoints across each player on each courseId. Something of the structure:
playerId = 123456
courseId = abcdef
total = 18
holes = (
([no] => 1, [holeNettPoints] => 2),
([no] => 2, [holeNettPoints] => 2),
([no] => 3, [holeNettPoints] => 3),
([no] => 4, [holeNettPoints] => 1),
([no] => 5, [holeNettPoints] => 2),
([no] => 6, [holeNettPoints] => 2),
([no] => 7, [holeNettPoints] => 2),
([no] => 8, [holeNettPoints] => 2),
([no] => 9, [holeNettPoints] => 2),
)
Ideally, the result would return a single document, and then I would be able to remove the playerId $match filter so that it would work across the many players who would play in each comp.
I would prefer that MongoDB uses aggregation to return the formatted and sorted result, so PHP can just loop through it's results.
My Answer:
PHP code for MongoDB aggregation combining both:
php
// Connect to MongoDB
$mongo = new MongoDB\Driver\Manager("mongodb://localhost:27017");
// Define the pipeline for aggregation
$pipeline = [
[
'$group' => [
'_id' => '$category',
'total' => ['$sum' => '$quantity']
]
],
[
'$sort' => ['total' => -1]
]
];
// Create the aggregation command
$command = new MongoDB\Driver\Command([
'aggregate' => 'products',
'pipeline' => $pipeline
]);
// Execute the aggregation command
$cursor = $mongo->executeCommand('database_name', $command);
// Get the results
$results = current($cursor->toArray());
// Print the results
print_r($results);
?>
This code connects to a MongoDB database, defines an aggregation pipeline to group by category and calculate the total quantity, sorts the results by total quantity in descending order, executes the aggregation command, and prints the results. You can modify the pipeline and aggregation command as needed for your specific use case.
Rate this post
4 of 5 based on 2895 votesComments