Multiple where filters on one property are ignored

[gabjauf]

Hello Loopback team!!
Just found an example on this page about querying data with multiple filters:
https://loopback.io/doc/en/lb3/Querying-data.html
filter(data, {where: {input: {gt: userInput.min, lt: userInput.max}}})

But tried it on my local LB3 app and could not make it work, it seems to take only the first argument (aka "gt").

Would be awesome to get it to work as it simplifies queries a lot (avoiding us to use the "and" operator)

Steps to reproduce

Typically on a new loopback 3 application:

• Create a bunch of users (with username filled) (ex: aa, ab, ac, ad, ae, ba, bb, bc, bd, be)
• In the explorer, try GET /Users with following filters

1. { "where": { "username": {"like": "a"}}, "fields": ["username"]} expects: aa, ab, ac, ad, ae, ba
2. { "where": { "username": {"neq": "ae"}}, "fields": ["username"]} expects: all but ae
3. { "where": { "username": {"neq": "ae", "like": "a"}}, "fields": ["username"]} expects: aa, ab, ac, ad, ba

For the gt / lt / lte / gte

1. { "where": { "username": { "gt": "b"}}, "fields": ["username"]} expects: ba, bb, bc, bd, be
2. { "where": { "username": { "lt": "bc"}}, "fields": ["username"]} expects: all but bc, bd, be
3. { "where": { "username": { "lt": "bc", "gt": "b"}}, "fields": ["username"]} expects: ba, bb

Current Behavior

Currently, results of 3rd request are exactly the same as 2nd.

Expected Behavior

We should not have the same results.

Additional information

darwin x64 12.16.3
├── [email protected]
├── [email protected]
├── [email protected]
├── [email protected]
├── [email protected]

Also, it seems there are 2 bugs in here when I use the VSCode debugger on the "buildWhere" method:

• The "like" clause disappears before buildWhere is called
• On the gt / gte / lt / lte issue, the correct where with all filters arrives at the buildWhere stage, but gets only the first filter in the query object at the end (on the example, it gets only the gt filter)
  It seems related to these lines:

if (cond && cond.constructor.name === 'Object') {
  regexOptions = cond.options;
  spec = Object.keys(cond)[0];
  cond = cond[spec];
}

mongodb.js -- line 958

Related Issues

Did not find any.

I hope it is accurate enough, do not hesitate to reach me 😉

[gabjauf]

users.json.zip
The created users to import in mongodb, so you can try quickly

[agnes512]

Related issue: loopbackio/loopback-next#5244

[gabjauf]

Hello 😸
Any update on this?
Did you get any opportunity to reproduce it?

[wangsenyan]

use like:
{ "where": {"and":[{"username":{"neq":"ae"}},{"username":{"like":"a"}}]} , "fields": ["username"]}

[agnes512]

Sorry for the late reply. I hadn't found time to investigate it. I quickly checked it and can reproduce the issue. As @wangsenyan suggested, the following would work:

{ "where": {"username": {"neq": "ae"},"userName": {"like": "a"}}, "fields": {"username":true}}

And you're right, the issue is causing by how we build the query (related code). That's why only the first operator gets applied. The issue looks valid to me as it is intuitive for MongoDB users. However, I don't think we bandwidth to improve it in a short time :(

[agnes512]

If you happen to have any proposal, feel free to submit a PR and ping me! Thanks!

[gabjauf]

Hello,

Thanks for the reply, I started working on something here => https://github.com/gabjauf/loopback-connector-mongodb
But I just found that the issue comes also from the juggler in the coerce function => https://github.com/strongloop/loopback-datasource-juggler/blob/2f867576b66fe535aa1f82bceaaa680ecd4eec6c/lib/model-utils.js#L362

Trying to find some bandwidth too haha 😄

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

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