Loopback: 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"
}

[raymondfeng]

Access token ids are hashed keys. They are not object ids.

Thanks,

---

Raymond Feng
Co-Founder and Architect @ StrongLoop, Inc.

StrongLoop http://strongloop.com/ makes it easy to develop APIs http://strongloop.com/mobile-application-development/loopback/ in Node, plus get DevOps capabilities http://strongloop.com/node-js-performance/strongops/ like monitoring, debugging and clustering.

On Feb 3, 2015, at 2:47 PM, Jonathan Rhone [email protected] wrote:

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"
}

—
Reply to this email directly or view it on GitHub https://github.com/strongloop/loopback/issues/274#issuecomment-72752616.

[mackwan84]

Any updates for this issue? It do cast the objectid into string when I update it via client side angularjs service $save

[jaideepdas]

Any update on this, @bajtos I see you have stated milestome #Epic: LoopBack 3.0 but is there a fix yet can I backport.

[bajtos]

I don't think there is any fix available yet.

[coox]

Thank you @jorgeramos for your workaround code, it really helped!
For what it's worth, the problem was also occurring on my side at create time, so I added the create method to the list of beforeRemote hooks.
Looking forward for a longer-term fix.

[riteshjagga]

I am trying to follow the workaround by the issue owner in a boot script
var ObjectID = require('mongodb').ObjectID;

I get the following error: _Cannot find module 'mongodb'_

Loopback Connector MongoDB is installed in the node_modules directory which uses mongodb.

How do I use mongodb in the boot script? I am quite new to Node.js coming from PHP, Flex and Java background, sorry for a trivial question.

[bajtos]

I am afraid there isn't any easy way how to get hold of the ObjectID constructor of the mongodb module used by loopback-connector-mongodb. You can try require('loopback-connector-mongodb/node_modules/mongodb').ObjectID, however that may break if your module layout is different (e.g. if you are using npm@3).

Perhaps we can modify loopback-connector-mongodb to export the native ObjectID constructor, something along the line

MongoDB.NativeObjectID = mongodb.ObjectID;

@raymondfeng thoughts?

[mike-aungsan]

I setup Model according to this example with HasManyThrough Relations.

https://docs.strongloop.com/display/public/LB/HasManyThrough+relations

In the explorer, http://localhost/explorer

I do not find any API that I can create appointment with physicianId and patientId

I look at appointment API, I look at patient API and physician API

Is it something missing or that need to be created manually?

Ref: https://github.com/strongloop/loopback/issues/1768

[superkhau]

Progress on this issue depends on https://github.com/strongloop/loopback-datasource-juggler/pull/778. However we should also take the longer term changes into consideration at before moving on this.

[TidyHuang]

Hello,

  I've encountered the same issue related to ObjectID. I define two models which are hasmany relation, 

I am using the foreign key (ID string) which is the results from first model as query filter to query information another model, the return values always empty array.
It is really appreciate anyone can help to answer this : does it mean we need manual casts the ID string to ObjectID type. I think we should keep the same behaviour as we use ID string to insert to table and it is saved as ObjectID type automatically.

[amiashraf]

As the issue entitled "ID values incorrectly stored as plain strings in relation tables for mongodb"
I have experienced two cases where this happening (i was working with loopback+juggler+angularjs):
Case 1. I have mistakenly didn't declare the relation of "belongs to" at commont/model_name.json and that's why my reference was storing as string.
Case 2. Add new is working fine but editing occur string reference.
Here the scenario, When I am using angular sdk and

ElectionCandidatePost.findById({
            id: id,
          filter:{
                  include:'currentElection'
                }
        })

replaced by

ElectionCandidatePost.findById({
            id: id
        })

Though I am using upsert function for adding/editing document and I don't know what is the relation with above api but It is now working fine now.

ElectionCandidatePost.upsert(electionCandidatePost).$promise
          .then(function (sfr) {
            console.log(sfr);
            CoreService.toastSuccess(
              gettextCatalog.getString('Election Candidate Post saved'),
              gettextCatalog.getString('Your Election Candidate Post is safe with us!')
            );
          })

Note: So I think include with filter cause the problem. If it is then need to have an attention.
I am using
"loopback-connector-mongodb": "1.11.3"
"loopback-datasource-juggler": "2.33.1"

[vko-online]

Any news?
Maybe we could use something like this?

"properties": {
    "companyId": {
      "type": "objectid" //or string? or whatever
    }
  }
"relations": {
    "company": {
      "type": "belongsTo",
      "model": "Company",
      "foreignKey": "companyId"
    }
  }

@bajtos Any ideas?

[albertolobrano]

I have the same issue. When i create the model the relation is saved as ObjectId but when i update with put/id it is converted into string ?

what is the solution so far?

[noderat]

While not ideal, what I've done is explicitly state that the property is an object ID through the model-name.js file. Here's an example for my extension of RoleMapping

'use strict';

const ObjectId = require('mongodb').ObjectId;

module.exports = function(roleMapping) {
  roleMapping.defineProperty('principalId', { type: ObjectId });
};

[albertolobrano]

Yes i am doing the same. Thanks for your suggestion

[kinhunt]

+1

[MantasR]

Had this problem as well...

I used the easy way..

in my package.json changed this:

    "loopback-connector-mongodb": "^1.13.0"
    "loopback-datasource-juggler": "^2.39.0"

to this:

    "loopback-connector-mongodb": "1.13.0"
    "loopback-datasource-juggler": "2.39.0"

Just if anyone is going through a release of project and can't wait for a fix as me :)

[blackvirus18]

+1

[waldemarzahn]

I just had the same problem and i found a very simple workaround:

One example:
A city belongs to a country.

This is not working:
_model-config.js_

...
"country": {
   "dataSource": "mongo",
   "public": true
},
"city": {
   "dataSource": "mongo",
   "public": true
}
...

This works:
_model-config.js_

...
"city": {
   "dataSource": "mongo",
   "public": true
},
"country": {
   "dataSource": "mongo",
   "public": true
}
...

The container model (in this example country) must be at the bottom. this way the foreign key will be parsed to an ObjectID and not an string.

[totalard]

Is it true?

[codejie]

I think I got the issue in Role-Mapping model~I could not insert principalId with ObjectId by Role relation to Role-Mapping.

{
"_id" : ObjectId("58c2185a6024ee2f1705d1ec"),
"principalType" : "USER",
"principalId" : "58c1380699220526baa9477b",
"roleId" : ObjectId("58c21117df1db02b1425882c")
}

[fabien]

This has been fixed recently, but it only works out-of-the-box with new Loopback apps, because the data has been stored in the legacy format in existing apps. You'd need to migrate that data yourself in that case.

[hyperd]

@codejie as @fabien pointed out the strictObjectIDCoercion is now available for RoleMapping model in new applications created using lb, apic etc.
In my case I had to add it manually to model-config.json which looks like this:

"RoleMapping": {
    "dataSource": "mydatasource",
    "public": false,
    "options": {
      "strictObjectIDCoercion": true
    }
  }
...

[HugoPoi]

@hyperd I recently upgrade loopback stuff and get problems with RoleMapping principalId type.
From

"loopback": "^2.22.0",
"loopback-boot": "^2.6.5",
"loopback-component-explorer": "^2.4.0",
"loopback-connector-mongodb": "~1.15.2",
"loopback-datasource-juggler": "^2.39.0",

To

"loopback": "2.38.x",
"loopback-boot": "2.24.x",
"loopback-component-explorer": "^2.7.0",
"loopback-connector-mongodb": "1.18.x",
"loopback-datasource-juggler": "2.54.x"

In RoleMapping collection principalId was string type. After upgrading RoleMapping doesn't work. User wasn't mapped to the correct Role. I migrate principalId to ObjectId type in RoleMapping collection and all work again without changes in config. And I haven't strictObjectIDCoercion set to true.

This is a breaking change, but I haven't identified where and when this occurs.

[dsl400]

+1

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

[stale[bot]]

This issue has been closed due to continued inactivity. Thank you for your understanding. If you believe this to be in error, please contact one of the code owners, listed in the CODEOWNERS file at the top-level of this repository.