Strapi Relations Not Working on Multiple Values

[SudeepPatel-0812]

Bug report

Required System information

• Node.js version: 14.21.1
• NPM version: 6.14.7
• Strapi version: 4.3.8
• Database: PostgreSQL
• Operating system: Linux

Describe the bug

So I have a scenario where I want to query results on the basis of relations. I have a 2 collections excluded sites & article, both have a many to many mapping. My query is get all the articles where excluded sites doesn't exists. It works fine when there is only one excluded site but fails when there are multiple excluded sites.

Steps to reproduce the behavior

1. Create 2 collections excluded sites & article (create site_id field in both).
2. Create many-to-many relation between excluded sites & articles.
3. Create some data in both, preferably make 2 articles in articles & 2 sites in excluded sites. (fill in the site_id as you please)
4. Search for the article with filters as: http://localhost:1337/api/articles?filters[site_id][$eq]=5678&filters[site_id][$eq]=1&filters[$or][0][excluded_sites][site_id][$null]=true&filters[$or][1][excluded_sites][site_id][$notIn]=5678
5. The result will contain all the results or the article will be returned which shouldn't be present.

Expected behavior

Since it's working for one excluded site , it should return all the results except excluded site mentioned in the notIn or notContains or notEquals. Example: excluded_sites[$notIn]=5678 should return all the articles where excluded sites does not contain 5678 in the relation.

You can see that the with the title "Global Article should not appear on 5678 is there which shouldn't be there in the results".

Screenshots

Code snippets

No code changes.

Additional context

Hi @PaulBratslavsky we had discussed this on the call let me know if the bug is not as clear as we saw. We can take a call.

[derrickmehaffy]

I think I see one problem here after I parsed your qs format back from LHS bracket syntax (easier to read)

{
  "filters": {
    "site_id": {
      "$eq": [
        "5678",
        "1"
      ]
    },
    "$or": [
      {
        "excluded_sites": {
          "site_id": {
            "$null": "true"
          }
        }
      },
      {
        "excluded_sites": {
          "site_id": {
            "$notIn": "5678"
          }
        }
      }
    ]
  }
}

The main issue I see here is how you are doing $OR for the $eq operator, this is the request structure I would suggest:

{
  "filters": {
    $or: [
      {
        site_id: {
          $eq: 5678
        }
      },
      {
        site_id: {
          $eq: 1
        }
      }
    ]
  }
}

Alternatively when checking for IDs specifically you can also use the $in operator and provide an array of IDs or integers

---

However that wasn't particularly the issue you ran into, let me test the other $or you were working on

[derrickmehaffy]

I tried to reproduce and kinda couldn't (confused here as one project didn't work) but this one does work (included DB dump)

https://github.com/derrickmehaffy/strapi-v4-test-14953

[github-actions[bot]]

This is a templated message

Hello @SudeepPatel-0812,

Thank you for reporting this bug, however we are unable to reproduce the issue you described given the information we have on hand. Can you please create a fresh project that you are able to reproduce the issue in, provide clear steps to reproduce this issue, and either upload this fresh project to a new GitHub repo or compress it into a .zip and upload it on this issue?

We would greatly appreciate your assistance with this, by working in a fresh project it will cut out any possible variables that might be unrelated. Please note that issues labeled with status: can not reproduce will be closed in 14 days if there is no activity.

Thank you!

[SudeepPatel-0812]

Created a fresh new project https://github.com/SudeepPatel-0812/issue-14593.git. This is the query that I am using "http://localhost:1337/api/articles?filters[sites][site_id][$notIn]=1234&populate=*". Now notIn shouldn't show the article where sites_id in sites is 1234 that article should not show up. In this project we shouldn't see TEST 3 in the results but is present

[derrickmehaffy]

Reproduction Video: https://www.loom.com/share/206df5780f664209a90e51d0ddecfb5f

[jhoward1994]

Hello there! After an internal review, we've found that the current behaviour of the $notIn filter (and others) doesn’t perform a partial match against cases with multiple relations. It will only exclude results if the filter value exactly matches the relational values, as demonstrated in Derrick's video. We think that to modify how these filters behave would risk breaking other user’s integrations.

However, as an enhancement we could add a new filter that can perform these partial matches for multiple relations. wdyt @derrickmehaffy ?

[derrickmehaffy]

Hello there! After an internal review, we've found that the current behaviour of the $notIn filter (and others) doesn’t perform a partial match against cases with multiple relations. It will only exclude results if the filter value exactly matches the relational values, as demonstrated in Derrick's video. We think that to modify how these filters behave would risk breaking other user’s integrations.

However, as an enhancement we could add a new filter that can perform these partial matches for multiple relations. wdyt @derrickmehaffy ?

Yeah I think that makes sense, what do you think this filter would be called?

Would we deprecate the old one later or keep it and just update the documentation?

Should we also create a new one for the $in as well?

[jhoward1994]

Naming is a bit tricky as we'd have to make the difference from the $contains filters clear.

wdyt about $valueIncluding & $valueNotIncluding?

I think we should keep the existing filters the same, imo they all have a valid purpose

[derrickmehaffy]

hmmmmm what about:

• fin (Fuzzy in)
• fnin (Fuzzy not in)

[SudeepPatel-0812]

Hello, I was wondering if you guys can use :

• inclusiveOf
• exclusiveOf (I think derrick mentioned that it's already used.)

:)

[derrickmehaffy]

Yeah exclusiveX would be basically what we have now.

[jhoward1994]

Sounds good. How about:

• $fIn
• $fNotIn

To keep the naming conventions similar to other filters?

[derrickmehaffy]

Sounds good. How about:

• $fIn
• $fNotIn

To keep the naming conventions similar to other filters?

Those make sense to me :)

[unrevised6419]

I have a problem and want to understand if is related to this issue. Not able to filter some contents using an include/exclude filter. From the reproduction video the problem it looks similar.

Include Contents, that have $heroIncludeTags related, and from them exclude those that have $heroExcludeTags related. 🤔

[derrickmehaffy]

I had a thought about the implementation of this in Knex (for the Strapi Engineers) and I'm wondering if we should use Knex's "having in " functions instead of the literal In:

https://knexjs.org/guide/query-builder.html#havingin

[derrickmehaffy]

@jhoward1994 what are your thoughts? Ideally I'd rather in/not in be the fuzzy options and instead make an ein enin (exclusive in / exclusive not in) but that would be a breaking change.

I think we need to run a POC for Knex having in option but it seems to be what we originally intended for those filters to do.

[derrickmehaffy]

Currently I believe we use the "where in" https://knexjs.org/guide/query-builder.html#wherein

[unrevised6419]

I've tried to patch in $fIn/$fNotIn to use having clauses, didn't get the results I wanted. Possibly my use case is different.

Here is the commit: https://github.com/iamandrewluca/strapi/commit/57fce484773f09483acf92de8049dcb0be8329be

[derrickmehaffy]

I've tried to patch in $fIn/$fNotIn to use having clauses, didn't get the results I wanted. Possibly my use case is different.

Here is the commit: iamandrewluca@57fce48

Which database were you using, I think depending on if it's MySQL or PG it might be different (which is extremely annoying)

[unrevised6419]

I was using Postgres 🤔

[Stuhl]

Anything new? Having the exact same issue. I thought about flipping the filtering by doing $not but that doesn't work either. I guess the only way is to make a custom controller?

Would be happy if this gets resolved soon.

[Boegie19]

@Stuhl If the $not keyword is not working please open a new issue for that spesificly.

[tod97]

Hi! Any news? I have this problem in my prod projects and I can't find any workaround. @derrickmehaffy @jhoward1994