"Pivot" table with MongoDB & PHP

Problem: I have a $cursor with documents as a “vertical” data source. I need to show this on a sub-table “horizontally”, i.e. to pivot the data.

This is possible with RDBMS, though requires extra querying to process.

What is the MongoDB / document DB equivalent? Or have I modelled completely incorrectly? (First use case…)

Each player has a “round” document, with each hole played as a sub-document. Also, can someone confirm if this model is embedded or linked? Currently, holes is an array of Objects, so I guess linked? Is this beneficial?

I would like to have a display like most websites or TV coverage, with the players name and score, and a lower table of the full round breakdown per hole.

The display would be something like Golf Channel’s:

Hi @Dan_Burt

I think you can use an aggregate and unwind stage to transform each array to an object.

Than lay it out on the grid…

Thanks
Pavel

Thanks @Pavel_Duchovny

My knowledge was that each query from the MongoDB would need (or already include) this aggregation. But I have already “received” the object from MongoDB, so I don’t see how I can aggregate or transform this sub-array. Would this be processed entirely in PHP? Any pointers for help?

With my current thinking, it would only be this holes array that would require this treatment.

Secondly, as part of my indirect question where I have used this sub-document / sub-object - would it be easier to use this as a normal array of data types and not another MongoDB Object? Pro’s, Con’s, etc?

Hi @Dan_Burt,

I am not sure I understand your second question… In my opinion the data model of keeping player statistics per game is ok as amount of holes is not dramatically large, so you won’t hit unbound arrays antipatterns.

Now I am not sure what do you mean you get the document in php as is, how do you query it? Via a php driver?

Why can’t you do:

db.players.aggregate([{"$match" : {playerId : ...}}, {"$unwind" : "$holes" }]);

Of course php syntax for aggs is a bit different.

If you want to do it on client side parse the document as you wish…

Thanks
Pavel

Currently, I already have a single query to get back a list of round documents, which contains this holes sub-document / sub-array.

The filter will change in future based upon the chosen competition. But I only have sample data currently, so not applying any filter currently. I am just applying sorting to the query. The PHP syntax for this is:

$collection = $client->golf->leaderboard;

$filter = [];
$options = ['sort' => ['score' => 1, 'throughHole' => -1]];
$cursor = $collection->find($filter, $options);

I then iterate through this $cursor object for my leaderboard display.

I had hoped that this same single query could be used, as the holes array is included in my resultset.

Your suggestion would be to use an entirely different query to get to this data. So this would require 2 queries (I expect)? As I need my first query to sort the main round documents first. Or can I complete both parts of the page display in the same aggregate query? (sorting first, then doing this extra aggregation / processing on the sub-documents)

The other option I was asking about it to just keep my original query, with no aggregation, and then do the processing in PHP instead. I guess this is a MongoDB forum, but was wondering if anyone could provide pointers for how to do this in PHP? My reading & searching all points to similar DB queries to perform the aggregation. Not to “pivot” an array variable.

Hi @Dan_Burt,

You can do the sorting and filtering in first stages of aggregation and unwind after.

So it will be one query does it all

Thanks
Pavel