Skip to content

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

Closed

Description

@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);
        }
    });

};

Activity

  1. innerdaze commented on May 28, 2014

    @innerdaze
    Author

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

  2. bajtos commented on Oct 22, 2014

    @bajtos
    Member

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

  3. Madsn commented on Nov 8, 2014

    @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.

  4. bajtos commented on Nov 10, 2014

    @bajtos
    Member

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

  5. danielbodnar commented on Nov 30, 2014

    @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?

  6. jorgeramos commented on Jan 12, 2015

    @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();
          });
        });
      });
    };
  7. raymondfeng commented on Jan 12, 2015

    @raymondfeng
    Member

    Sorry I missed it. I'll check soon.

  8. jorgeramos commented on Jan 12, 2015

    @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...

  9. danielbodnar commented on Jan 12, 2015

    @danielbodnar

    Thanks @jorgeramos, looks much more elegant.

  10. raymondfeng commented on Jan 12, 2015

    @raymondfeng
    Member

    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.

  11. Tarabyte commented on Feb 2, 2015

    @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.
  12. jrhone commented on Feb 3, 2015

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

  13. raymondfeng commented on Feb 3, 2015

    @raymondfeng
    Member

    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 notifications@github.com 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 #274 (comment).

  14. 49 remaining items

  15. fabien commented on Mar 10, 2017

    @fabien
    Contributor

    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.

  16. hyperd commented on Mar 24, 2017

    @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
        }
      }
    ...
    
  17. removed their assignment
    on Mar 27, 2017
  18. HugoPoi commented on May 17, 2017

    @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.

  19. dsl400 commented on Jun 5, 2017

    @dsl400

    +1

  20. stale commented on Mar 11, 2020

    @stale

    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.

  21. stale commented on Mar 26, 2020

    @stale

    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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Type

    No type

    Projects

    No projects

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions