Count Metadata doesn't take filtering into account

[sebqq]

Hello.

I have this situation, where {entity}/count REST endpoint with deep filtering and using '_or' clause is not working properly. Count value is even bigger than number of items that are stored in my db (of that type) at time of request.

So we have these two tables: Dogs and Colors. Every dog can have multiple colors assigned using following relation:

Please look at following picture to see, what query params are comming from our Frontend to strapi endpoint (endpoint is overriden only for purposes of this test. We normally use pre-generated endpoint):

As you can see, I have 11 dogs in my development DB, but count endpoint has provided 15 as result.

Cause of the issue is the most probably a fact, that some of dogs have multiple colors assigned. When I use only 1 color per dog, thne the filtering problem dissapears.

Does anyone know, what could be the reason for this behaviour?

Thank you so much and have a nice day!

System

• Strapi version: 3.1.4
• Database: tried on SQLite and PostgreSQL
• Operating system: macOS Catalina

[derrickmehaffy]

Hello,

I tested this on all supported databases and it appears to be working fine (not your custom controller but just the built in default count routes)

[emahuni]

I am having this issue as well

[emahuni]

le me try to have a go at it

[derrickmehaffy]

@emahuni can you provide any steps to reproduce the issue because I haven't been able to.

[emahuni]

Can definitely reproduce on my machine in the app, but can't put my finger on how to do it elsewhere, yet. Here is the funny thing. The same code filtering the same model, but just different records, is producing the bug and another is not. ie:

query convs {
  conversationsConnection(
    where: {
      assignedTo: "115"
      disposition: { id: "136", departments_in: ["79", "83"] }
    }
  ) {
    aggregate {
      count
    }
  }
}

is counting 4 instead of 2 (note that it's always doubling when this happens), but the following:

query convs2 {
  conversationsConnection(
    where: {
      assignedTo: "115"
      disposition: { id: "108", departments_in: ["79", "83"] }
    }
  ) {
    aggregate {
      count
    }
  }
}

is counting 12 as expected. The difference between the 2 queries is just that disposition association.

So I want to go through each record to see any anomalies that I can associate with the bug, because it seems like there is something about the records that is causing the code to misinterpret things. Locating the issue in the data can help with identifying and fixing the problem in the code.

[emahuni]

important models and associations:

Conversation.settings.json:

{
    "attributes": {
      "assignedTo": {
        "model": "employee",
        "via": "assignedConversations"
      },
      "disposition": {
        "model": "disposition"
      }
    }
}

Employee.settings.json:

  {
    "attributes": {
      "assignedConversations": {
        "via": "assignedTo",
        "collection": "conversation"
      }
    }
  }

Disposition.settings.json:

  {
    "attributes": {
      "departments": {
        "via": "dispositions",
        "collection": "department",
        "dominant": true
      }
    }
  },

Department.settings.json:

  {
    "attributes": {
      "dispositions": {
        "collection": "disposition",
        "via": "departments"
      }
    }
  }

[emahuni]

I think I now have a lead. You see, the records retrieved by the first query with disposition 136 are doubling becoz its disposition is associated with both departments it asked for, but the second query for disposition 108 is associated with only 1 of the departments it asked for.

One of the records' values for the first query (producing bug), notice departments 79 and 83 are present in the following:

"values": [
        {
          "id": "392",
          "disposition": {
            "id": "136",
            "departments": [
              {
                "id": "44"
              },
              {
                "id": "79"
              },
              {
                "id": "80"
              },
              {
                "id": "81"
              },
              {
                "id": "83"
              }
            ]
          }
        },
]

but not in one of the records' values for the second query, notice only department 79 is present in the following (working query):

"values": [
        {
          "id": "4",
          "disposition": {
            "id": "108",
            "departments": [
              {
                "id": "44"
              },
              {
                "id": "79"
              }
            ]
          }
        },
]

It works as expected if I remove department 83 and change the query to:

query convs {
  conversationsConnection(
    where: {
      assignedTo: "115"
      disposition: { id: "136", departments_in: ["79"] }
    }
  ) {
    aggregate {
      count
    }
  }
}

This means the code is counting for each department found when there is an _in or maybe also _or filter. They both seem to work from the same principles and am sure is the same code doing this. This is where we start from. Somehow the code is not counting correctly when it's like that. It needs to break from the count if one of the _in / _or filter conditions are met.

So, to reproduce, create those models with those associations and records as depicted. You should be able to reproduce it.

[emahuni]

Just to assert this, if I ask for an additional department "80" to make a total of 3 departments associated with disposition 136, it triples. ie:

query convs {
  conversationsConnection(
    where: {
      assignedTo: "115"
      disposition: { id: "136", departments_in: ["79", "83","80"] }
    }
  ) {
    aggregate {
      count
    }
  }
}

That produces 6 instead of 2. That's correct count of '2' for each department found. Happens for each department I add that is associated with disposition (see above values list).

[iksent]

I probably have the same bug:

But the simplier one works OK:

My scheme is:

Specialty has many Services
Services has many Therapies
Doctor has one Specialty and has many Therapies (for overriding specialty's therapies)

[derrickmehaffy]

@emahuni @iksent This issue is only for REST not GraphQL, there is a related issue for GraphQL related count aggregations here: #7547

[iksent]

Any updates on this?

There is probably no solution now for fetching "relational" items with count (neither with aggregate.count, nor with this guide due to the bug).

So it's impossible to create paginated lists.

[Limonische]

So is there any workaround for REST?