Summary
The title may not have summarized this problem up, but I wasn't sure how to put it.
Simply, I have a tabular dataset that is lying within a Mongo database. When a user opens a page on a website, the PHP backend will fetch the document from Mongo and display it in an Excel-esque way to the user.
The user can then play around with the data, change the cells as they like, remove rows, remove columns, resort rows, resort columns, etc.. Once they are finished, they click a save button. This is where the problem is tricky for me.
I don't want to immediately apply changes to the database when a column/row is removed/added or resorted, but I want to be able to click the "save" button and for it all to be saved into a document.
Background
Using PHP 7.3, MongoDB with the driver class (https://www.php.net/manual/en/book.mongodb.php)
I've tried two methods. One doesn't do what I need it to, the other does, but with a caveat.
The first method was the use of the update
method. However, whenever I resorted or deleted columns/rows from the webpage and clicked the "save" button, these fields wouldn't get resorted or deleted from Mongo.
The second was much simpler and easier. I simply deleted the old collection, and bulk writed the new data into a new collection. It's really simple, but I don't want to run into an issue where it deletes the data and then has an issue writing the data into a new collection.
Code
Here's my bulk write way to write to the database:
// Prepare to write to Mongo.
$manager->executeCommand('db', new \MongoDB\Driver\Command(["drop" => "test_b"]));
$bulk = new MongoDB\Driver\BulkWrite;
foreach ($dataset as $entry) {
$count = 0;
$arr = array();
foreach ($entry as $info) {
$arr[$fields[$count]] = $info;
$count++;
}
$bulk->insert($arr);
}
And here is the update method:
// Prepare to write to Mongo.
$bulk = new MongoDB\Driver\BulkWrite;
foreach ($dataset as $entry) {
$count = 0;
$arr = array();
$entryId = "temp";
foreach ($entry as $info) {
if ($count == 0) $entryId = $info;
else $arr[$fields[$count]] = $info;
$count++;
}
$objectId = new MongoDB\BSON\ObjectId("$entryId");
$bulk->update(["_id" => $objectId], ['$set' => $arr], ['multi' => false, 'upsert' => false]);
}
Conclusion
I'm not sure of the best way to approach this problem. I can't seem to get the update way to work. Perhaps I am using it incorrectly or maybe I don't know the proper function to use. If I go the way of just deleting the collection and rewriting it, there's always the possibility of having a writing error and then losing all of the data. In this case I could clone the previous data and then write the new data, and if the write is successful, then kill the clone?
I'm really not trying to keep track of which columns are removed or resorted and would like a simple approach of "replace old data with the new data", but in a secure fashion. Any suggestions?