Sails: Finding with criteria { attrId: instance.id } return empty array

[betacar]

This issue/question was posted in the SailsJS Google Group, but no one could answer it.

I'm trying to query a model to retrieve a collection using the .find() with attributes criteria, but it does not work.

Here is my model:

// Model name: Service.js 

module.exports = {
  tableName: 'services',
  attributes: {
    // Models fields
    id: {
      type: 'integer', // <--- Why Sails see this as string?
      required: true,
      index: true
    },
    userId: {
      type: 'string', // <--- I want to find all docs with a criteria over this field
      required: true,
      index: true
    },
    provider: {
      type: 'string',
      required: true,
      contains: ['twitter']
    },
    uname: {
      type: 'string',
      required: true,
      index: true
    },
    uemail: {
      type: 'string',
      required: true
    }
  }
};

When doing a blank find, the model behave as expected:

sails> Service.find().done(function(e,s) { if (e) return console.error(e); return console.log(s); });
undefined
sails> [ { provider: 'twitter',
    uname: 'somename',
    user_id: 51f88ddc5d7967808b000001,
    id: '5402952' } ]

However, when using a criteria is not:

sails> Service.find({userId: '51f88ddc5d7967808b000001' }).done(function(e,s) { if (e) return console.error(e); return console.log(s); });
undefined
sails> []
...
sails> Service.find({user_id: '51f88ddc5d7967808b000001' }).done(function(e,s) { if (e) return console.error(e); return console.log(s); });
undefined
sails> []
...
sails> Service.find().done(function(e,s) { if (e) return console.error(e); return console.log(typeof s[0].user_id); });
undefined
sails> object // <--- This a object?

On the other hand, I declared the doc ID as integer. But Sails see it like a string. Why?

sails> Service.find().done(function(e,s) { if (e) return console.error(e); return console.log(typeof s[0].id); });
undefined
sails> string

Any ideas? I'm using SailsJS 0.9.3 with the MongoDB adapter.

[vanetix]

This could correspond with #66, and adapters injecting specific types. Just glancing through the code, it looks like it's attempting to match a string against a MongoDB ObjectId.

@particlebanana Perhaps in a future release, we could have a injected types such as:

attributes: {
    id: {
      type: 'objectid',
      required: true,
      index: true
    }
}

The only issue with this that comes to mind is transactions against multiple adapters.

[betacar]

@vanetix Any idea on doing a temporary fix?

[vanetix]

I haven't tested this - but you can talk to the MongoDB collection directly with Collection.native for example:

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

Service.native().done(function(e, collection) { 
  if (e) throw e;

  collection.find({ userId: new ObjectID('51f88ddc5d7967808b000001') }, console.log);
});

However for a definite answer I would wait for @particlebanana to chime in.

[particlebanana]

Because you are using mongo the ID property has special meaning, it's the primary key for a row. It's probably best to just let mongo handle it like you are doing with your User model id. If you want to use integers you can and it should work flawlessly minus the fact that when you get the result back the id will be in string form it seems. There is probably somewhere in the sails-mongo adapter that does a greedy .toString() but it is stored in mongo as an integer.

Your model above works fine. This is what I have stored in mongo:

{ 
    "userId" : "51f88ddc5d7967808b000001", 
    "provider" : "twitter", 
    "uname" : "foo",
    "uemail" : "foobar", 
    "createdAt" : ISODate("2013-08-19T16:48:44.221Z"), 
    "updatedAt" : ISODate("2013-08-19T16:48:44.221Z"), 
    "_id" : 1234
 }

And with the following query I get this back:

Service.find({id: 1234 }).done(function(e,s) {
      if (e) console.error(e);
      return res.json(s);
});

// Will Return

[ { userId: '51f88ddc5d7967808b000001',
    provider: 'twitter',
    uname: 'foo',
    uemail: 'foobar',
    createdAt: Mon Aug 19 2013 11:48:44 GMT-0500 (CDT),
    updatedAt: Mon Aug 19 2013 11:48:44 GMT-0500 (CDT),
    id: '1234' } ]

It looks like you may have some stale data that has user_id if you drop the collection in mongo and insert a new record you shouldn't be getting a user_id property.

I also tried with the following and was able to query by userId:

Service.find({userId: '51f88ddc5d7967808b000001' }).done(function(e,s) {
      if (e) console.error(e);
      return res.json(s);
});

// Will Return

[ { userId: '51f88ddc5d7967808b000001',
    provider: 'twitter',
    uname: 'foo',
    uemail: 'foobar',
    createdAt: Mon Aug 19 2013 11:48:44 GMT-0500 (CDT),
    updatedAt: Mon Aug 19 2013 11:48:44 GMT-0500 (CDT),
    id: '1234' } ]

[particlebanana]

Sails-Mongo is calling toString() on the id attribute when it normalizes it from _id to id.

https://github.com/balderdashy/sails-mongo/blob/master/lib/utils.js#L35

I can look at that in a bit.

[betacar]

@particlebanana Actually is a legacy database. I have nearly 2 million documents on it. I cannot drop any of them. Should I change the the field name back to user_id?

[particlebanana]

Ya you either need to make your attributes fit your schema or you can add a columnName attribute to map it to the schema. I also wouldn't recommend developing against a production database! Is your legacy database not using mongo ObjectId's as primary keys?

module.exports = {
  tableName: 'services',
  attributes: {
    // Models fields
    id: {
      type: 'integer', // <--- Why Sails see this as string?
      required: true,
      index: true
    },
    userId: {
      type: 'string', // <--- I want to find all docs with a criteria over this field
      required: true,
      index: true,
      columnName: 'user_id'
    },
    provider: {
      type: 'string',
      required: true,
      contains: ['twitter']
    },
    uname: {
      type: 'string',
      required: true,
      index: true
    },
    uemail: {
      type: 'string',
      required: true
    }
  }
};

[betacar]

@particlebanana I'm not developing over a production database, but is a copy of it. The production database has almost 20Gb of data.

I applied that fix and it does not work:

sails> Service.find({userId: '51f88ddc5d7967808b000001' }).done(function(e,s) { if (e) return console.error(e); return console.log(s); })
undefined
sails> []

[particlebanana]

Can you post the mongo document for that so I can see what's in there? I'll try and get a patch out for the integer id thing tonight if I can.

[betacar]

@particlebanana I posted above. But no problem:

{ 
  "_id" : 1234567,
  "provider" : "twitter",
  "uname" : "name",
  "user_id" : ObjectId( "51f88ddc5d7967808b000001" ) 
}

[particlebanana]

Doing some house cleaning. If this is still an issue ping me and I will re-open it.

[henryboldi]

I'm having the same issue. The following works properly in the user controller

User.find({id: req.param('id')}).limit(1).exec( function foundHack(err, hack){
  if(err) return next(err);
  if (!hack) return next();

      res.json({
        user: hack,
      });

    });

But the following returns an empty array like the op when nested within the hack controller

Hacks.find({id: req.param('id')}).limit(1).exec( function foundHack(err, hack){
  if(err) return next(err);
  if (!hack) return next();
  User.find({id:hack.owner}).exec( function foundUser(err, user){
    if(err) return next(err);
    if (!user) return next();

        res.json({
          user: user,
          hack: hack
        });

      }); 
}); 

I have nothing regarding the _id in the user model and I'm on version 0.10.0-rc4

[kavuri]

@9o Got this pointer from japel over IRC

----8<----
japel> MyDoc.findOne("53d618143383b94f2f34b951").exec(function(err, res) { console.log(res); }
https://github.com/balderdashy/sails-docs/blob/master/reference/waterline/models/findOne.md
"Any string arguments passed must be the ID of the record. If you are trying to find an attribute that is an array, you must wrap it in an additional set of brackets otherwise Waterline will think you want to perform an inQuery." ...
----8<----

Think this helps

[henryboldi]

@kavuri Thanks!