Error: Undefined attribute level operator $and at applyOperator

[PoliPH]

Bug report

Required System information

• Node.js version: 20.12-alpine
• NPM version: 10.5.0
• Strapi version: 4.23.1
• Database: postgressql
• Operating system: linux
• Is your project Javascript or Typescript: Typescript

Describe the bug

In the new version of Strapi (4.23.1) when we call to entityService.findMany we get the following error:

Error: Undefined attribute level operator $and at applyOperator (/opt/node_modules/@strapi/database/dist/index.js:3738:13) at 
Builder.<anonymous> (/opt/node_modules/@strapi/database/dist/index.js:3615:11) at compileCallback 
(/opt/node_modules/knex/lib/formatter/formatterUtils.js:7:12) at rawOrFn 
(/opt/node_modules/knex/lib/formatter/wrappingFormatter.js:225:7) at QueryCompiler_MySQL.whereWrapped 
(/opt/node_modules/knex/lib/query/querycompiler.js:1095:17) at QueryCompiler_MySQL.where 
(/opt/node_modules/knex/lib/query/querycompiler.js:584:34) at QueryCompiler_MySQL.toSQL 
(/opt/node_modules/knex/lib/query/querycompiler.js:75:29) at compileCallback 
(/opt/node_modules/knex/lib/formatter/formatterUtils.js:13:19) at rawOrFn 
(/opt/node_modules/knex/lib/formatter/wrappingFormatter.js:225:7) at QueryCompiler_MySQL.whereWrapped 
(/opt/node_modules/knex/lib/query/querycompiler.js:1095:17) `

It is happen when you execute a subquery $and in a relation, in my case when I try to filter a relation bannerLocation with the "$and" operator.

  "filters": {
    "$and": [
      {  "end_at": { "$gte": "2024-04-15T22:00:00.000Z"  } },
      {  "end_at": { "$lte": "2024-04-18T22:00:00.000Z" } },
      {  "locale": "es"   },
      {  "order": 2  },
      {  "bannerLocation": {   "$and": [   { "code": "banner_home"  }  ] }
      }
    ]
  }

Strapi dependencies

    "@strapi/plugin-i18n": "4.23.1",
    "@strapi/plugin-users-permissions": "4.23.1",
    "@strapi/provider-upload-local": "4.23.1",
    "@strapi/strapi": "4.23.1",

If I change all these dependencies to 4.22.1 it works as expected.

Steps to reproduce the behavior

1. Execute await this.strapi.entityService!.findMany(this.collection, payload) with a filter with $and in a subquery relation
2. get the error

Expected behavior

Success sql.

Screenshots

Code snippets

Code that execute the error:

const payload = {
  "filters": {
    "$and": [
      {  "end_at": { "$gte": "2024-04-15T22:00:00.000Z"  } },
      {  "end_at": { "$lte": "2024-04-18T22:00:00.000Z" } },
      {  "locale": "es"   },
      {  "order": 2  },
      {  "bannerLocation": {   "$and": [   { "code": "banner_home"  }  ] }
      }
    ]
  },
  "populate": {
    "bannerLocation": {
      "filters": {},
      "populate": "*"
    },
    "BannerType": {   "filters": {},  "populate": "*" }
  }
}

await this.strapi.entityService!.findMany(this.collection, payload)

[PoliPH]

I found the error... It seems that in the 4.23.0 version of Strapi you have to include the "filters" attribute when you subquery into a related entity, so I change this:

  {  "bannerLocation": {   "$and": [   { "code": "banner_home"  }  ] }

to this:

  {  "bannerLocation": {   "filters": {"$and": [   { "code": "banner_home"  }  ] }}

In previos versions of Strapi the first code works well.

[PoliPH]

In older versions like 4.16.2 the "filters" attribute in subquery does not work, it is a breaking change that should be specified in the documentation. https://docs.strapi.io/dev-docs/migration-guides

[willempie96]

I think this is a side effect of this PR:
#19971

This is something that is also extremely hard to spot in you codebase, so even if this is something that was mentioned in the migration guides it would be really hard to fix this without checking every use of the filters.
In my opinion this should actually be reverted to the old way, because this should be a dx improvement but seems to be mostly a breaking change .

[Pablo-Aldana]

I completely agree. If this is intended, it should be flagged as a breaking change. Since there is nothing in the documentation, and it directly impacts the entire code base, it's likely to cause breakages.

I’d like to know what is the Strapi team position in here before update our code base or wait until this is fix before upgrading to a newer version @derrickmehaffy

🦾🦾🦾

[derrickmehaffy]

Will need @alexandrebodin to confirm. Outside my realm to say one way or the other.

[alexandrebodin]

Hi, This was not an intended side effect, I'll correct it asap

[alexandrebodin]

ℹ️ The fix has been released in 4.23.2 as a patch and will also be in the 4.24.1 release tomorrow