Filter not working correctly

[sskhokhar]

Consider the following model definition:

Model A:

"properties": {
        "initiatorId": {
            "type": "any"   //ID of another model
        },
        "sharedUserId": {
            "type": "any"  //ID of another model
        }
    }

When I apply filter to model A in explorer like

{"where" : {"initiatorId" : "123123123"}}

I get the results but when I apply this filter

{"where" : {"sharedUserId" : "44553322"}}

I get empty array

Even this behavior is seen in filter like

{"where" : {"and" : [ {"initiatorId" : "123123123"},{"sharedUserId" : "44553322"}]}}

I get empty array.

I tried changing data types from "any" to "string" but still it doesn't work Please suggest me something on this.

[sebastianfelipe]

What endpoint are you using this filter?

[sskhokhar]

Hi, Sorry for late reply. Actually this is happening almost on every endpoint and in every project I use loopback. I dont know why this happens. I am not sure whether to use "String" or "Any" datatype to store MongoID.

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

[sunnyspan]

Got similar issue, when I use {"where":{"id":XX}} it returns the correct result. But when I try other clause like ("where":{"itemId":AAA}} it returns empty array. Further test the id clause with some invalid id i.e. {"where":{"id":YY}}, it incorrectly return an array of full result including all items. I'm using loopback 3.8.0

[pradeep4sure]

Got similar issue, using orgId in Content as foreign key of id in Organisation collection. Using Content.find({where:{ orgId }}) returning [] empty array. Getting results when using other fields like Content.find({where:{ name }}).

[jasoncys]

Got similar issue. Here are the details:

• LoopBack version: 4.0.0 (generator-loopback@5.4.0 loopback-workspace@4.0.0)
• Node version: 8.9.1
• MongoDB version: 3.6.0
• Ubuntu version: 16.04 LTS
• ACL: Anyone can access to anything

Custom Participant model inherited from the built-in User with the following attributes:

    "user_type": {
      "type": "string",
      "required": true
    },
    "parentUserId": {
      "type": "string"
    }

where parentUserId is populated with User.id.

Receive empty array ([]) where queried using http://localhost:3000/api/Participants?filter={"where":{"parentUserId":"<id set by using User.id>"}}&access_token="<access_token>". No problem if "parentUserId" is replaced with "id" along with the corresponding id value.

If used with "WHERE" instead of "where", it returns all entities in Participants.

Please help. Thanks.

[obo20]

i'm receiving this exact issue as well. Has anybody found a way around this?

[jonathan-casarrubias]

I suddenly started to present this issue as well,

{"where": { "chainId": "598c71f8a61ed73d00756212"}}

it returns empty array :(. I believe this will be a datasource-juggler issue.

[sskhokhar]

The workaround to this problem is to use "Like" clause. e.g

{"where" : {"chainId" : { "like" : "598c71f8a61ed73d00756212"}}}

I hope this helps you all.

[jasoncys]

@sskhokhar It works.

[sunnyspan]

Unfortunately the "like" clause workaround doesn't work in my case

[bajtos]

Hello, thank you all for participating in this discussion. Our bandwidth is very limited, therefore it would tremendously help us if somebody could create a sample application that will reproduce the issue - ideally a test that seeds the database with the necessary data, makes the HTTP call(s) and asserts the expected result. See http://loopback.io/doc/en/contrib/Reporting-issues.html#bug-report. The simpler the test cause you can come up with, the easier it will be to identify the root cause.

[alexthewilde]

I've encountered the same issue: if you want to filter by {where: { somePropertyId: id }} then somePropertyId needs to be an ObjectId or it will return an empty array.

My mistake has been to declare somePropertyId with type:string in the model json. As soon as I removed the property and instead declared a relation on someProperty, loopback automatically generates somePropertyId with the correct ObjectId type.

[suisun2015]

I occasionally face such problems, too.

[petrgazarov]

Hi all,

Here is a simple app which reproduces the issue using mongodb connector: https://github.com/petrgazarov/loopback-sandbox-issue-3593-repro

Mongodb connector automatically converts string properties that look like ids to ObjectID types. This is documented in the docs.

So then

1. a simple where filter such as { "where": { "referenceId": "5a8c71cb3b800d0001f9b146" } } returns an empty array.

2. a filter where referenceId does not have a default id format works as expected: { "where": { "referenceId": "1234" } }.

When I set strictObjectIDCoercion flag to true in model definition file, both filters worked as expected. This flag prevents default coercion to ObjectID type.

{
  "name": "myModelName",
  "base": "PersistedModel",
  "idInjection": false,
  "options": {
    "validateUpsert": true,
    "strictObjectIDCoercion": true
  },
  ...
}

Loopback docs should probably mention this gotcha as it is not clear that not setting this flag breaks the where filter.

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

[choychris]

@petrgazarov Thank you so much for pointing out. Was facing the same issue and using an hour to figure out how to fix it. Saved my day !

Edit: It happens occasionally, for some models I do not add "strictObjectIDCoercion": true, the issue does not appear. While for some models which is no problem previously, then the issue suddenly appear. This is weird.

[AliMirlou]

@petrgazarov Shouldn't this flag be true by default in a web framework? This behavior is actually dangerous!

[anupammaurya]

Yes where in string works for me!

"where": {query: detailsResult}

Student.app.models.locker.findOne({
          fields: ['first_name', 'last_name', 'number'],
          order: 'id',
          "where": {lockerId: results.findStudentDetails[0].lockerId},
        }, (err, result) => {
         console.log('result...:', result);
          return callback(null, result);
  });

[shashijangir]

in Loopback 4 this is not working. { filter: { where: { memberId: userId , status: 1 } } };

[shashijangir]

i did try to make it ObjectId also but it does not work.

[sebastianfelipe]

LoopBack 3 still failing

[webocs]

This works in LB3, but it doesn't make sense IMO. There are two possible things going on here, doc is wrong and doesn't state that findOne needs an object with the filter property or doc is correct and findOne is bugged.

{ filter: { where: { memberId: userId , status: 1 } } };

It'd be nice to have a confirmation on this issue so we can either fix the docs or fix the issue.

[luisalejandrofigueredo]

dont use where only { "id":2} work fine

[laxmi1707]

The workaround to this problem is to use "Like" clause. e.g

{"where" : {"chainId" : { "like" : "598c71f8a61ed73d00756212"}}}

I hope this helps you all.

It worked for me

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

[mitchelangelo]

I am having the same issue it only seems to occur for integers when I went into the model and changed the attribute in question to a string [where] works as expected.

[Maniarasan]

I have the same issue. I have used the work around to use "like" temporarily. I have attached the package.json file to identify if package versions has got anything to do with it.

{ "name": "watersense", "version": "1.0.0", "description": "watersense", "keywords": [ "loopback-application", "loopback" ], "main": "index.js", "engines": { "node": ">=10" }, "nodemonConfig": { "ignore": [ "**/*.test.ts", "**/*.spec.ts", ".git", "node_modules" ], "watch": [ "src" ], "exec": "npm start", "ext": "ts" }, "scripts": { "build": "lb-tsc", "build:watch": "lb-tsc --watch", "clean": "lb-clean dist *.tsbuildinfo", "lint": "npm run prettier:check && npm run eslint", "lint:fix": "npm run eslint:fix && npm run prettier:fix", "prettier:cli": "lb-prettier \"**/*.ts\" \"**/*.js\"", "prettier:check": "npm run prettier:cli -- -l", "prettier:fix": "npm run prettier:cli -- --write", "eslint": "lb-eslint --report-unused-disable-directives .", "eslint:fix": "npm run eslint -- --fix", "pretest": "npm run clean && npm run build", "test": "lb-mocha --allow-console-logs \"dist/__tests__\"", "posttest": "npm run lint", "test:dev": "lb-mocha --allow-console-logs dist/__tests__/**/*.js && npm run posttest", "docker:build": "docker build -t watersense .", "docker:run": "docker run -p 3000:3000 -d watersense", "migrate": "node ./dist/migrate", "prestart": "npm run build", "start": "npm run prettier:fix && node -r source-map-support/register .", "dev": "nodemon --watch src --ignore 'src/**/*.spec.ts' --exec npm start", "prepublishOnly": "npm run test" }, "repository": { "type": "git" }, "author": "", "license": "", "files": [ "README.md", "index.js", "index.d.ts", "dist", "src", "!*/__tests__" ], "dependencies": { "@loopback/authentication": "^4.2.5", "@loopback/boot": "^2.0.2", "@loopback/context": "^3.8.1", "@loopback/core": "^2.2.0", "@loopback/openapi-v3": "^3.1.1", "@loopback/repository": "^2.0.2", "@loopback/rest": "^3.1.0", "@loopback/rest-explorer": "^2.0.2", "@loopback/service-proxy": "^2.0.2", "@types/jsonwebtoken": "8.3.9", "axios": "^0.19.2", "bcryptjs": "2.4.3", "isemail": "3.2.0", "jsonwebtoken": "8.5.1", "loopback-connector-mongodb": "5.0.1", "openapi-to-graphql-cli": "^2.1.0", "tslib": "^1.10.0" }, "devDependencies": { "@loopback/build": "^5.0.0", "@loopback/eslint-config": "^6.0.2", "@loopback/testlab": "^2.0.2", "@types/bcryptjs": "2.4.2", "@types/node": "^10.17.17", "@typescript-eslint/eslint-plugin": "^2.25.0", "@typescript-eslint/parser": "^2.25.0", "eslint": "^6.8.0", "eslint-config-prettier": "^6.10.1", "eslint-plugin-eslint-plugin": "^2.2.1", "eslint-plugin-mocha": "^6.3.0", "nodemon": "^2.0.4", "source-map-support": "^0.5.16", "typescript": "~3.8.3" } }

[flaviogoncalves]

I got to work in this way http://127.0.0.1:3000/users?filter[where][domain]=domain100

without quotes in loopback4

[AmanMandloi]

Hi all,

Here is a simple app which reproduces the issue using mongodb connector: https://github.com/petrgazarov/loopback-sandbox-issue-3593-repro

Mongodb connector automatically converts string properties that look like ids to ObjectID types. This is documented in the docs.

So then

1. a simple where filter such as { "where": { "referenceId": "5a8c71cb3b800d0001f9b146" } } returns an empty array.
2. a filter where referenceId does not have a default id format works as expected: { "where": { "referenceId": "1234" } }.

When I set strictObjectIDCoercion flag to true in model definition file, both filters worked as expected. This flag prevents default coercion to ObjectID type.

{
  "name": "myModelName",
  "base": "PersistedModel",
  "idInjection": false,
  "options": {
    "validateUpsert": true,
    "strictObjectIDCoercion": true
  },
  ...
}

Loopback docs should probably mention this gotcha as it is not clear that not setting this flag breaks the where filter.

Thanks this worked for me also.