ID values incorrectly stored as plain strings in relation tables for mongodb

[innerdaze]

Outline

When creating many-many relations between models for a mongodb data source, entries are stored in the relation table as per this example:

{ 
  "_id" : ObjectId("537b7f5eb723553355816de6"),
  "partId" : "5379f3444d596bea29987bef",
  "jobId" : "5379e59d6fd568ba272a940c"
}

The job record in referred to in this record is:

{
  "_id" : ObjectId("5379e59d6fd568ba272a940c"),
  "title" : "test", 
  ...
}

When querying the relation, a query such as job.parts() will return an empty array because the job._id property with a value of ObjectId("5379e59d6fd568ba272a940c") will not match the jobId property with a value of "5379e59d6fd568ba272a940c".

The first example (the relation object) is always created this way when using either of the following methods:

job.parts.add(part, cb);

or

var PartJob = Part.relations.job.modelThrough;

PartJob.create({
    partId: "5379f3444d596bea29987bef",
    jobId: "5379e59d6fd568ba272a940c"
}, cb);

Workaround

As it stands, the only way found to make a working many-many relation is to use the following method:

var ObjectID = require('mongodb').ObjectID;
var PartJob = Part.relations.job.modelThrough;

PartJob.create({
    partId: ObjectID("5379f3444d596bea29987bef"),
    jobId: ObjectID("5379e59d6fd568ba272a940c")
}, cb);

This creates the relation object:

{ 
  "_id" : ObjectId("537b7f5eb723553355816de6"),
  "partId" : ObjectId("5379f3444d596bea29987bef"),
  "jobId" : ObjectId("5379e59d6fd568ba272a940c")
}

Proposed Solution

Whilst this creates a usable many-many relation, it creates an extra step in the process and invalidates the job.parts.add method, so the casting of the the string ID to an ObjectID should probably occur behind the scenes. This would also avoid having to change the existing public API.

Also (and this should probably have it's own github issue), if you wanted to create a many-many relation over a REST API, it's more efficient to simply pass the IDs of the object you wish to connect and create the relation object directly onto the relation table.

This (as Raymond has pointed out) is a rather hacky approach and in terms of providing a clean fix for the above issue becomes unfeasible as you would still be forced to manually cast the ID properties.

I propose a static method for each relationship on each model class for creating a relation that simply takes the 2 IDs to create a relation for. With this approach, the static method could perform the ID casting without the developer having to concern themselves with it.

For example:

var ObjectID = require('mongodb').ObjectID;
var PartJob = Job.relations.part.modelThrough;

Job.attachPart = function(jobId, partId, callback){

    PartJob.create({
        partId: ObjectID(partId),
        jobId: ObjectID(jobId)
    }, function(err, result){
        if (err) {
            callback(err);
        } else {
            callback(null, result);
        }
    });

};

[innerdaze]

I have tested and can confirm that this issue also exists for the create method on a hasAndBelongsTo relationship

[bajtos]

@lsdriscoll can you reproduce the problem using the latest version of loopback-connector-mongodb and loopback-datasource-juggler?

[Madsn]

I'm having this issue, using the following packages:

    "grunt-loopback-angular": "^1.1.0",
    "loopback": "^2.7.0",
    "loopback-boot": "^2.3.0",
    "loopback-component-storage": "^1.0.5",
    "loopback-connector-mongodb": "^1.4.4",

Everything works fine when using the memory connector, but with mongo I can't find entities via relationships because the ID is saved in mongodb as a string.

[bajtos]

@raymondfeng @fabien could you PTAL? You know more about juggler and connector internals than I do.

[danielbodnar]

This may not be the same exact issue, but it's similar.

I ran into this relations ObjectID issue whenever PUTing or POSTing an object with one of the belongsTo still attached. So for example, say I did a Car.find({include:'owner'}), then modified the car without detaching / deleting the owner object, the datasource juggler would replace the fk with a string and remove the object.

My hacky solution was to create a beforeRemote hook for upsert for every model via a bootscript, like so:

var ObjectID = require('mongodb').ObjectID;

module.exports = function(app) {
    var models = app.models();
    models.forEach(function(Model) {
        console.log('Setting up beforeRemote for: ', Model.modelName);

        Model.beforeRemote('upsert', function(ctx, m, next) {
            var relations = Model.settings.relations;
            for (var k in relations) {
                if (relations[k].type === 'belongsTo' && ctx.args.data[k] && (typeof ctx.args.data[k] === 'object' && ctx.args.data[k].id)) {
                    ctx.args.data[relations[k].foreignKey] = new ObjectID(ctx.args.data[k].id)
                    delete ctx.args.data[k];
                }
            }
            next();
        });
    });
};

Can anyone recommend a more efficient way of doing this?

[jorgeramos]

Hi @danielbodnar - your code above helped me solve a similar issue. The code below iterates through all model relations converting them to proper mongo ObjectIDs if the data property is either an object with an id or just a string. It also fixes dates that were getting stored as string instead of ISODate. As it's going through the data object, it checks to see if the prop is a nested model and tries to fix it's properties as well. You can choose which remote methods to affect by editing the array with method names. I'm not sure what's the root of the problem or if this is the best solution but I hope it helps...

var ObjectID = require('mongodb').ObjectID;

module.exports = function(app) {
  var models = app.models();
  //
  var fixRelations = function(Model, ctx){
    var relations = Model.settings.relations;
    for(var k in relations) {
      var fk = (relations[k].foreignKey != '') ? relations[k].foreignKey : k+"Id";
      if(relations[k].type === 'belongsTo'){
        if(ctx.args.data[k] && typeof ctx.args.data[k] === 'object' && ctx.args.data[k].id) { //<-- should add a mongodb objectId regex here
          ctx.args.data[fk] = new ObjectID(ctx.args.data[k].id)
          delete ctx.args.data[k];
        }else if(ctx.args.data[fk] && typeof ctx.args.data[fk] === 'string' && String(ctx.args.data[fk].length) == 24){ //<-- should add a mongodb objectId regex here instead
          ctx.args.data[fk] = new ObjectID(ctx.args.data[fk]);
        };
      };
    };
  };
  //
  var fixProps = function(Model, ctx){
    var getAction = function(type){
      switch(type){
        case 'date' :
          return 'date';
          break;
        default :
          if(Model.app.models[type]) return 'model';
          else return null;
      };
    };
    var processData = function(data, props){
      for(var k in props){
        var type = props[k].type;
        if(type && typeof type === 'string'){
          var action = getAction(props[k].type);
          if(action == 'date' && data[k] && typeof data[k] === 'string') data[k] = new Date(data[k]); //<-- should add some type of date regex here
          else if(action == 'model' && data[k]) processData(data[k], Model.app.models[type].definition.rawProperties);
        };
      };
    };
    processData(ctx.args.data, Model.definition.rawProperties);
  };
  //
  models.forEach(function(Model) {
    ["upsert", "updateAll", "updateAttributes", "destroyById", "destroyAll"].forEach(function(methodName){
      Model.beforeRemote(methodName, function(ctx, m, next) {
        console.log('remote-hooks.beforeRemote(' + ctx.methodString + ')');
        fixRelations(Model, ctx);
        fixProps(Model, ctx);
        next();
      });
    });
  });
};

[raymondfeng]

Sorry I missed it. I'll check soon.

[jorgeramos]

i updated the code slightly to provide proper foreign key when the relation is defined without a custom foreign key. Also, it would be great if the date and object id ran through a regex before attempting to process. At the very least there should be some try-and-catch to prevent the app from erroring out...

[danielbodnar]

Thanks @jorgeramos, looks much more elegant.

[raymondfeng]

The type of a foreign key property will be updated to match to the corresponding primary key by LoopBack. I'll check the add() method to see why the value is stored as string in the joined collection.

[Tarabyte]

Any news on the issue?
I have almost the same problem with belongsTo relation. Post belongsTo Category.

• When I save Post instance for the first time using Post.upsert categoryId is saved as ObjectId. Which is an expected behaviour of course.
• When I edit the post using the same Post.upsert categoryId is converted to a String. Which results in various problems later when one needs to query posts by category.

[jrhone]

For what it's worth, AccessToken id's are currently generated as Strings instead of ObjectIds.. different bug?

"id": {
  "type": "string",
  "id": true
}

{
"ttl" : 1209600,
"created" : ISODate("2014-12-01T22:50:18.752Z"),
"userId" : ObjectId("5464f1f8df5ea75545c39d1c"),
"_id" : "uqdHTCgre9LD3XQHeH2BUAkW8iVxWSsxGEKmKaAjjRBm9nxNheRSTAbzn8lxcaHZ"
}