Loopback: isSet query operator

[ritch]

We should add support for something like this:

{
  where: {
    someField: {
      isSet: false
    }
  }
}

This would allow you to query for undefined fields using $isset in mongodb and NULL in SQL databases.

[ritch]

@kraman @bajtos @raymondfeng @fabien

[bajtos]

Well if we can't use { where: { someField: null } }, then isSet makes sense.

[raymondfeng]

We can use { where: { someField: null } } today but the semantics is different for NoSQL DBs. For example, in Mongo,

someField: null ==> someField: {$type: 10}
someField: {isSet: false} ==> someField: {$exists: 10}

I'm fine to introduce connector specific extensions. Each connector should validate such extensions and report errors if a given operator or syntax is not supported.

[bajtos]

We can use { where: { someField: null } } today but the semantics is different for NoSQL DBs.

Yeah, I was sort of expecting that, in wich case null is not a solution. Since LoopBack is promising to provide single API working on all databases, we need a solution that works consistently in all supported databases.

[marcellodesales]

@bajtos @raymondfeng is there any timeframe when this feature makes the cut?

[bajtos]

@marcellodesales not really. Can you contribute the feature yourself?

[jsheely]

This already works using the exists method.

// REST Url
// /api/<modelName>?filter[where][<propertyName>][exists]=<true|false>

modelName.find({
    where: {
        propertyName: {
            exists: true
        }
    }
}, callback)

The key is https://github.com/strongloop/loopback-connector-mongodb/blob/master/lib/mongodb.js#L488 where it will apply the $ sign to any property it doesn't understand as an else case.

[kraman]

@jsheely That is specific to mongodb. would be nice if this could be generalized to all dbs.

[jsheely]

@kraman Agreed. I just wanted to be clear that this feature technically can work today. This and the related issues make it seem like it wasn't possible.

While it is not a baked part of the abstracted API it is very nice that it can fall down into the specific implementations of the provider and still be handled.

[raymondfeng]

@kraman What's your expectation for relational DBs? Do you interpret exists: true as IS NOT NULL?

[kraman]

@raymondfeng yes, typically it would be IS NOT NULL

[marcellodesales]

@jsheely I agree with that being available in the API, as well as with @kraman around "IS NOT NULL" on relational db...

[kblcuk]

@jsheely, the approach you mentioned doesn't work with current datasource-juggler when it coerces where clause:
https://github.com/strongloop/loopback-datasource-juggler/blob/128665f1bd403b94a08a55a230a496e838c47347/lib/dao.js#L926

[dhirenderbp]

nothing is working for the existence of a field in a document.

[arealmaas]

+1. Having the same probem as @kblcuk

[pmoelgaard]

Isn't this a very common use case, I cant believe there is no solution ?

[kblcuk]

@pmoelgaard we've worked around it by introducing isDateSet property for model (defaulting to false since default for date in question is null), with which we can search for models which don't have date set.
This is suboptimal, though, and hopefully this issue will be eventually resolved.

[rawrmaan]

This is redonk. High priority for my team +5

[gausie]

The automatic passthrough of unrecognised properties actually doesn't work for this on MongoDB.

[gausie]

The reason for this bug lies with this line

https://github.com/strongloop/loopback-datasource-juggler/blob/master/lib/dao.js#L1238

In my case, the property I am inspecting is of boolean type. My { exists: true/false } object will be converted to true by this line because DataType is the Boolean constructor.

[gausie]

A possible solution for this would be to have the coercion skipped if the value is an object?

[barocsi]

For example see this

{last_vote_date: {exists: true}

throws an error

stack: "Error: Invalid date: [object Object]↵    at DateType 

So how should this be resolved? Any workaround?

[kblcuk]

@barocsi https://github.com/strongloop/loopback/issues/1009#issuecomment-138228604

Another option I guess would be to init that date during beforeSave hook to be 0 ('1970-01-01', or something like that), and make your code to treat it like null.

[barocsi]

Thanks! But pre-filling the with database with unneeded data, just for the sake of a inappropriate (re-)implementation of an efficient mongodb query wrapper seems a bad practice.

[jsheely]

Currently the DAO breaks this functionality for the mongoDB fall through $exists check.

} else if (!((operator === 'like' || operator === 'nlike') && val instanceof RegExp)) {
          //val = DataType(val);  // Remove this use the object instead of passing "[object Object]" as the value
}

https://github.com/strongloop/loopback-datasource-juggler/blob/master/lib/dao.js#L1496

[lautarodragan]

This is needed for couchbase as well! Any updates on the implementation of this feature?

[sundeepgupta]

I'm finding that the nlike operator is working as a workaround in my particular case, which is for a MongoDB connector and string type property.

Account.find({where: {analyticsId: {nlike: ''}}})

[Ijmir]

+1
can't query date field with exists - throws error when trying to cast it to date.
any update on this?

[tamitutor]

+1
Trying to send a query via the REST api. Want to return Mongo documents where a particular field does NOT exist.

[tamitutor]

Never mind, figured out I was spelling "exists" as "exist"...For any one interested here is how to do it with stringified JSON via REST (with MongoDb as the data repo):

{"include": ["from", "to", {"relation": "childMessages", "scope": {"order": "created DESC", "include": ["from", "to"]}}, "parentMessage"], "order": "created DESC", "where": {"parentId": {"exists": false}}}

[Sanbornzhang]

maybe sometime filter have $ should continue ?
cause I always need field:{$operator: value}
if field instanceof Model and key match ^$ then continue?

[idushes]

module.exports = function(server) { let APIConnector; APIConnector = server.datasources.mongo.connector; return APIConnector.observe('before execute', function(ctx, next) { if (ctx && ctx.req && Array.isArray(ctx.req.params)) { ctx.req.params.forEach(function (param) { Object.keys(param).forEach(function (key) { if (param[key] && param[key]["$type"] === 10) param[key] = null; }); }) } next(); }); };

[idushes]

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

[jawadrehman]

got this error with a date type as well. Have to add null data :(

[akvaliya]

I am also getting this issue.
Works for exists:true, but doesn't work for exists:false

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

[bartlettpsj]

It is still an issue!

[SippieCup]

Guessing now that we are in active LTS this wont be fixed :(