Crud: Combined $and and $or in search request generates only AND operators

[leo-jnesis]

Hi,

When I query this :

{
  "$and": [
    {
      "createdAt": {
        "$lte": "2020-03-13T15:49:37.289Z"
      }
    },
    {
      "$or": [
        {
          "name": {
            "$contL": "a"
          },
          "email": {
            "$contL": "a"
          }
        }
      ]
    }
  ]
}

I get this generated query :

SELECT * FROM "users" "User" WHERE "User"."createdAt" <= $1 AND ("User"."name" ILIKE $2 AND "User"."email" ILIKE $3) -- PARAMETERS: ["2020-03-13T15:50:59.592Z","%a%","%a%"]

I was expecting this :

SELECT * FROM "users" "User" WHERE "User"."createdAt" <= $1 AND ("User"."name" ILIKE $2 OR "User"."email" ILIKE $3) -- PARAMETERS: ["2020-03-13T15:50:59.592Z","%a%","%a%"]

am I doing something wrong in the search params ?

Thanks !

[leo-jnesis]

Any thoughts on this ?

[kuklei]

I also need to know this. In our case we are using a global filter and is messing our $or. If I just use this with $and filters it works OK but once I introduce an $or in a query param that messes the $and and $or combination. Any thoughts on this

[kuklei]

another post that tries to come around this issue https://github.com/nestjsx/crud/issues/437

[hakimio]

The issue is that nesting in the first post is not correct (or part is nested incorrectly). It should be:

{
    "$and": [{
        "createdAt": {
            "$lte": "2020-03-13T15:49:37.289Z"
        }
    }, {
        "$or": [{
            "name": {
                "$contL": "a"
            }
        }, {
            "email": {
                "$contL": "a"
            }
        }]
    }]
}

I've tested this type of nested conditions with the latest version of CRUD library and it works as expected for me.

To avoid this kind of issues in the future I suggest writing search conditions the following way:

{
    $and: [{
        name: {$excl: 'Second'}
    }, {
        $or: [{
            field1: {$lt: 4}
        }, {
            field2: {$gt: 6}
        }]
    }]
}

[leo-jnesis]

Of course, my bad!

Thanks a lot