How to do conditional update in mongo db?

Question

I am trying to update document's sub document like below lines of code

$bulkbatch = new MongoDB\Driver\BulkWrite(['ordered' => true]);  

$subDocumentStatus = array(
     "Status" => array(
          "CurrentStatus" => $this->EmployeeStatus,
          "StatusDate" => new MongoDB\BSON\UTCDateTime(strtotime($this->EmployeeStatusDate)*1000),
          "IsActive" => $IsActive 
      )
);

$bulkbatch->update( 
    array(
         '_id' => new MongoDB\BSON\ObjectID($this->id)
    ),
    array(
         '$addToSet' => $subDocumentStatus
    ), 
    [
         'multi' => true,  
         'upsert' => false
    ]
);

$this->manager->executeBulkWrite($this->collection, $bulkbatch);  

I have document like

{
       "EmployeeNumber": "123456",
       "Instructor": NumberInt(1),
       "Department": ObjectId("5a8173e6d2ccda13240015a4"),
       "FirstName": "Faiza",
       "MiddleName": "Yousuf",

       "Status": [
                 {
                 "CurrentStatus": "Joined",
                 "StatusDate": ISODate("2018-02-28T23:00:00.0Z"),
                 "IsActive": NumberInt(1)
                 }
              ],
       ...
       ...
}

If $this->EmployeeStatus contains "Joined" and $this->EmployeeStatusDate contains some different values like for above document it contains "2019-05-28T23:00:00.0Z" (other than previous value) then it should be updated.

The above code is inserting another sub document with values like

  "Status": [
      {
        "CurrentStatus": "Joined",
        "StatusDate": ISODate("2018-02-28T23:00:00.0Z"),
        "IsActive": NumberInt(1) 
      },
      {
       "CurrentStatus": "Joined",
       "StatusDate": ISODate("2019-05-28T23:00:00.0Z"),
       "IsActive": NumberInt(1) 
      } 
    ],
     ...

I want that in the case of Joined the existing sub document should be updated.

Please help!!!

Answer 1

There is a solution with two Mongo calls; first tries to update an existing sub-document; if no sub-document is modified then one is inserted in the main document.

Here is a unit-test that proves the solution works (the main function is updateOrInsert):

require_once __DIR__ . '/MongoTestHelper.php';

use MongoDB\BSON\ObjectID;
use MongoDB\BSON\UTCDateTime;
use tests\Dudulina\MongoTestHelper;

class TmpTest extends \PHPUnit_Framework_TestCase
{
    /** @var \MongoDB\Collection */
    private $collection;

    /** @var ObjectID */
    private $_id;

    public function test_subdocumentExists()
    {
        $statusDate = new UTCDateTime(strtotime("2018-02-28T23:00:00.0Z"));
        $employeeStatus = "Joined";

        /**
         * Test data
         */
        $this->collection->insertOne([
            "_id"            => $this->_id,
            "EmployeeNumber" => "123456",
            "Instructor"     => 1,
            "Department"     => new ObjectID("5a8173e6d2ccda13240015a4"),
            "FirstName"      => "Faiza",
            "MiddleName"     => "Yousuf",

            "Status" => [
                [
                    "CurrentStatus" => $employeeStatus,
                    "StatusDate"    => $statusDate,
                    "IsActive"      => 1,
                ],
            ],
        ]);

        /**
         * the real test
         */
        $this->updateOrInsert($employeeStatus, $statusDate, 0);


        /**
         * we verify the execution
         */
        $document = $this->collection->findOne([
            "_id" => $this->_id,
        ]);

        $this->assertCount(1, $document['Status']);
        $this->assertSame(0, $document['Status'][0]['IsActive']);

    }

    public function test_subdocumentDoesNotExists()
    {
        $statusDate = new UTCDateTime(strtotime("2018-02-28T23:00:00.0Z"));
        $employeeStatus = "Joined";

        /**
         * we insert some test data
         */
        $this->collection->insertOne([
            "_id"            => $this->_id,
            "EmployeeNumber" => "123456",
            "Instructor"     => 1,
            "Department"     => new ObjectID("5a8173e6d2ccda13240015a4"),
            "FirstName"      => "Faiza",
            "MiddleName"     => "Yousuf",

            "Status" => [
                [
                    "CurrentStatus" => 'SomeOtherStatus',
                    "StatusDate"    => new UTCDateTime(strtotime("2010-02-28T23:00:00.0Z")),
                    "IsActive"      => 1,
                ],
            ],
        ]);

        /**
         * the real test
         */
        $this->updateOrInsert($employeeStatus, $statusDate, 0);

        /**
         * we verify the execution
         */
        $document = $this->collection->findOne([
            "_id" => $this->_id,
        ]);

        /**
         * now there are 2 subdocuments inserted
         */
        $this->assertCount(2, $document['Status']);

        /**
         * we verify the second document
         */
        $secondSubDocument = $document['Status'][1];

        $this->assertSame(0, $secondSubDocument['IsActive']);
        $this->assertSame('Joined', $secondSubDocument['CurrentStatus']);
        $this->assertTrue($statusDate->toDateTime()->getTimestamp() === $secondSubDocument['StatusDate']->toDateTime()->getTimestamp());

    }

    /**
     * The function that you should do
     */
    private function updateOrInsert($employeeStatus, $statusDate, $isActive)
    {
        $updateResult = $this->collection->updateOne([
            "_id"                  => $this->_id,
            "Status.CurrentStatus" => $employeeStatus,
            "StatusDate"           => ['$ne' => $statusDate],
        ], [
            '$set' => [
                'Status.$.StatusDate' => $statusDate,
                'Status.$.IsActive'   => $isActive,
            ],
        ], [
            'upsert' => false,
        ]);

        if ($updateResult->getModifiedCount() === 0) {
            $this->collection->updateOne([
                "_id" => $this->_id,
            ], [
                '$addToSet' => [
                    'Status' => [
                        "CurrentStatus" => $employeeStatus,
                        "StatusDate"    => $statusDate,
                        "IsActive"      => $isActive,
                    ],
                ],
            ], [
                'upsert' => false,
            ]);
        }
    }

    protected function setUp()
    {
        $this->collection = (new MongoTestHelper())->selectCollection('eventStore');
        $this->collection->deleteMany([]);
        $this->_id = new ObjectID('47ecbc36e8fb4b50662adcf9');
    }
}