Yii 2.0: Auto-update denormalized attributes with MongoDb and Yii2

3 followers

The scenario

You have different collections in MongoDb with de-normalized attributes. As you may know, due to its not-join nature, MongoDb tends to encourage repetition of the same value in different collections (de-normalization). In this MongoDb is opposed to a more SQL-like approach where you usually reference only the ID of the value with a foreign key.

In SQL-like DBs

For example this scenario applies to a SQL-like approach:

tbl_user

  • id: int(11)

  • username: varchar(128)

  • address_id: int(11)

where the address_id field is a foreign id to:

tbl_address

  • id: int(11)

  • street: varchar(255)

  • city_id: int(11)

where the city_id field is a foreign key to the tbl_city etc...

In MongoDb

In Mongo it's a whole different story and, depending on your requirements, you may want to follow this approach:

collection user

{
    _id:12345
    username:"peterGower",
    address:{
        city:"someCity",
        street:"someStreet"
    }
}

and, if you want to check that the field "city" is always the same to avoid "sameCity" "SameCity" or "SAMECITY" to be considered different, you may want to create a collection location.city that you'll check for existence before inserting the "city" attribute in the user collection.

collection location.city

{
    _id:12345,
    name:"someCity"
}

All this is well and good, but what happens if we modify the name of a city to our referenced attributes? Well, nothing, meaning that city.name:"newCityName" will not affect user.address.city:"oldCityName" . Having a de-normalized DB without foreignKeys has its cost, but we may solve this with a simple behaviour in Yii2.

Sync referenced data with a behaviour

Before starting we may want to pay attention to one thing: this approach can become a huge bottleneck in a collection with a lot of updates on the referenced fields. If your collection's referenced attribute is subject to frequent changes, you may want to reference the foreignId (in MongoDb this approach is called linking)

The Behaviour

/**
 * @author edoardo849 <edoardo.s@daviom.com>
 */
 
namespace common\behaviors;
 
 
use yii\base\Behavior;
use yii\base\ErrorException;
use yii\db\BaseActiveRecord;
use Yii;
use yii\helpers\Json;
use yii\mongodb\file\ActiveRecord as MongoGridFsAR;
 
class DependenciesBehavior extends Behavior
{
    public $attributes = [];
 
    public $mongoTimeout = 100000000;
 
    public $multiple = true;
 
    public $errorOnDelete = true;
 
    public function events()
    {
        return [
            BaseActiveRecord::EVENT_BEFORE_UPDATE => 'onBeforeUpdate',
            BaseActiveRecord::EVENT_BEFORE_DELETE => 'onBeforeDelete'
        ];
    }
 
    public function onBeforeUpdate()
    {
        $changes = $this->getChanges();
 
        if (!empty($changes)) {
            foreach ($changes as $change) {
                if (isset($this->attributes[$change[0]])) {
                    $this->updateDependency($change[0], $change[1], $change[2]);
                }
            }
        }
    }
 
    private function getChanges()
    {
        $changes = [];
        foreach ($this->owner->attributes as $key => $newValue) {
            if (isset($this->owner->oldAttributes[$key])) {
                $oldValue = $this->owner->oldAttributes[$key];
                if ($oldValue != $newValue) {
                    $changes[] = [$key, $oldValue, $newValue];
                }
            }
        }
        return $changes;
    }
 
    private function updateDependency($key, $oldValue, $newValue)
    {
        $dependencies = $this->attributes[$key];
        foreach ($dependencies as $dependency) {
            /**
             * @var \yii\mongodb\ActiveRecord $model
             */
            $model = new $dependency['class'];
            $targetAttribute = $dependency['targetAttribute'];
            $collectionName = $model::collectionName();
            if ($model instanceof MongoGridFsAR) {
                $collectionName = $collectionName . '.files';
            }
            /**
             * @var \MongoCollection $mongoCommand
             */
            $mongoCommand = Yii::$app->mongodb->getCollection($collectionName);
            $mongoCommand->update([$targetAttribute => $oldValue], ['$set' => [$targetAttribute => $newValue]], ['multiple' => $this->multiple, 'timeout' => $this->mongoTimeout]);
        }
    }
 
    public function onBeforeDelete()
    {
        if ($this->errorOnDelete) {
            $changes = $this->getChanges();
            if (!empty($changes)) {
                throw new ErrorException('The following attributes has dependencies and cannot be deleted: ' . Json::encode($changes), 500);
            }
        }
 
    }
 
}

The Model

In the model that has references (so, following our previous example, we may want to include this behaviour in the location.city model, which is \common\models\LocationCity.php in our case)

public function behaviors()
    {
        return [
            'dependencies'=>[
                'class'=>DependenciesBehavior::className(),
                'attributes'=>[
                    'name'=>[
                        ['class'=>User::className(), 'targetAttribute'=>'address.city'],
                                                ['class'=>Company::className(), 'targetAttribute'=>'registeredOffice.city'],
                    ]
                ]
            ]
        ];
    }

Here we say: the attribute location.city.name has references in 2 collections: user.address.city and company.registeredOffice.city . So, every time you modify the "name" attribute, update the dependencies.

How the behaviour works

In order not to waste precious memory and CPU time, the behaviour will not do anything unless the specified value has changed. For example, if we perform an update in the field location.city.shortName, the behaviour will recognise that the attribute location.city.name is unchanged and will do nothing.

On the contrary, when we do change location.city.name the behaviour will perform a multi-update of all the specified references.

In addition, it will throw an error if you try to delete an attribute that has references in other collections (like the ON UPDATE RESTRICT of MySql).

There are some configuration options that you may want to modify:

  • $mongoTimeout: integer, for large collections, it's useful to extend the standard timeout of the PHP's MongoCursor (30 sec)

  • $multiple: boolean, whether to update all the referenced attributes or not (default:true)

  • $errorOnDelete: boolean, throws an error if you try to delete referenced attributes (default:false)

Be the first person to leave a comment

Please to leave your comment.

Write new article