Query with AND & OR combination is not working

[vineet-suri]

Maintainers' note This has been re-posted elsewhere: https://loopbackio.slack.com/archives/C01177XQN8N/p1605242283269100

Steps to reproduce

I am trying to execute this code for the find repository method (which accepts FILTER)

let filter = {
  where: {
    and: [
      {
        or: [
          {userId: '3dc67467-1d37-a1de-9669-5565d5f356fe'},
          {connectionUserId: '3dc67467-1d37-a1de-9669-5565d5f356fe'},
        ],
      },
      {status: 'ACCEPTED'},
    ],
  },
};

this.connectionRepository.find(filter);

Current Behavior

I am getting this SQL for the above query:

SELECT "id","user_id","connection_user_id","status" FROM "main"."connections" WHERE ("status"=$1) ORDER BY "id"
Parameters: ["ACCEPTED"]

OR clause is completely skipped .

Expected Behavior

However, when i am trying to execute same query for count method of repository (which accepts WHERE clause, the correct SQL is executed. Code:

this.connectionRepository.count({
  and: [
    {
      or: [
        {userId: '3dc67467-1d37-a1de-9669-5565d5f356fe'},
        {connectionUserId: '3dc67467-1d37-a1de-9669-5565d5f356fe'},
      ],
    },
    {status: 'ACCEPTED'},
  ],
});

SQL query executed

SELECT count(*) as "cnt" FROM "main"."connections" WHERE (("user_id"=$1) OR ("connection_user_id"=$2)) AND ("status"=$3)
Parameters: ["3dc67467-1d37-a1de-9669-5565d5f356fe","3dc67467-1d37-a1de-9669-5565d5f356fe","ACCEPTED"]

In find method also, the complete SQL query, including OR clause should be executed.

Additional information

linux x64 12.15.0

[dhmlau]

@vineet-suri, there are related fixes in PR #466 and strongloop/loopback-connector#185. Could you please verify it if it works for you? Thanks.

[achrinza]

Closing as stale / likely solved. Feel free to request re-opening this issue if you believe this was a mistake.