Root filter can't match multiple conditions in nested o2m field data

[AndreKR]

Describe the Bug

filter: {
  _and: [
    {relationship: {field: {_eq: "foo"}}},
    {relationship: {field: {_eq: "bar"}}},
  ]
}

gets interpreted as

filter: {
  _and: [
    {relationship: {_and: [ {field: {_eq: "foo"}}, {field: {_eq: "bar"}} ]}},
  ]
}

To Reproduce

As an example I'm using the data from the Many-to-One example in the docs, i.e. cities and countries. I have extended the countries table with a field language to demonstrate a point.

Finding a country whose language is "Italian" and that has a city called "Rome" works fine:

query {
  countries(
    filter: {
      _and: [
        {cities: {name: {_eq: "Rome"}}},
        {language: {_eq: "Italian"}},
      ]
    }
  ) {
    name
    cities {
      name
    }
  }
}

Result:

{
  "data": {
    "countries": [
      {
        "name": "Italy",
        "cities": [
          {
            "name": "Naples"
          },
          {
            "name": "Rome"
          },
          {
            "name": "Venice"
          }
        ]
      }
    ]
  }
}

SQL query:

select `countries`.`name`, `countries`.`country_id`
from `countries`
         inner join (select distinct `countries`.`country_id`, `countries`.`country_id` as `sort_upfve`
                     from `countries`
                              left join `cities` as `mpctx` on `countries`.`country_id` = `mpctx`.`country_id`
                     where ((`mpctx`.`name` = ? and `countries`.`language` = ?))
                     order by `countries`.`country_id` asc
                     limit ?) as `inner` on `countries`.`country_id` = `inner`.`country_id`
order by `inner`.`sort_upfve` asc
[Rome, Italian, 100]

However, finding a country that has a city called "Rome" and has a city called Venice doesn't work:

query {
  countries(
    filter: {
      _and: [
        {cities: {name: {_eq: "Rome"}}},
        {cities: {name: {_eq: "Venice"}}},
      ]
    }
  ) {
    name
    cities {
      name
    }
  }
}

Result:

{
  "data": {
    "countries": []
  }
}

SQL query:

select `countries`.`name`, `countries`.`country_id`
from `countries`
         inner join (select distinct `countries`.`country_id`, `countries`.`country_id` as `sort_vtcba`
                     from `countries`
                              left join `cities` as `btdid` on `countries`.`country_id` = `btdid`.`country_id`
                     where ((`btdid`.`name` = ? and `btdid`.`name` = ?))
                     order by `countries`.`country_id` asc
                     limit ?) as `inner` on `countries`.`country_id` = `inner`.`country_id`
order by `inner`.`sort_vtcba` asc
[Rome, Venice, 100]

It appears that the filter is erroneously interpreted as:

query {
  countries(
    filter: {
        cities: {_and: [ {name: {_eq: "Rome"}}, {name: {_eq: "Venice"}} ]}
    }
  ) {
    name
    cities {
      name
    }
  }
}

which has pretty much the same SQL query:

select `countries`.`name`, `countries`.`country_id`
from `countries`
         inner join (select distinct `countries`.`country_id`, `countries`.`country_id` as `sort_gqwda`
                     from `countries`
                              left join `cities` as `aslfz` on `countries`.`country_id` = `aslfz`.`country_id`
                     where ((`aslfz`.`name` = ? and `aslfz`.`name` = ?))
                     order by `countries`.`country_id` asc
                     limit ?) as `inner` on `countries`.`country_id` = `inner`.`country_id`
order by `inner`.`sort_gqwda` asc;
[Rome, Venice, 100];

Directus Version

v10.2.0

Hosting Strategy

Self-Hosted (Custom)

[cwalton]

Any update on this? I'm stuck on v 9.21.2 because of this issue. Any thoughts on a workaround until it's addressed?

[rijkvanzanten]

@cwalton How are you trying to use it at present time? An and on the same field twice like:

filter: {
  _and: [
    {relationship: {field: {_eq: "foo"}}},
    {relationship: {field: {_eq: "bar"}}},
  ]
}

doesn't really make sense in context, as it will always be false. If you're trying to use an or with multiple, you should try an _in instead. If you're trying to combine a gt and lte, you could use between instead.

[AndreKR]

I gave an example... take the data from the Many-to-One example in the Directus docs and try to build a GraphQL query that finds a country that has a city called "Rome" and also a city called "Venice". I don't see how that could be done with an _in.

[rijkvanzanten]

Yeah but that's with the nested one to many as you're searching through a nested cities, I'm curious to see if @cwalton's usage is the same, or if he ran into the same bug with a different use case 👍🏻

[cwalton]

It's the same situation. It's not just on a code based query, you get the issue in the console as well. If I try to filter on a many to one relationship.

problem is that it works on the old versions. something happened to break this behavior.

[rijkvanzanten]

@cwalton Can you share what filter you're using?

[AndreKR]

By the way, this issue also affects the REST API and the UI:

[cwalton]

Yes, EXACTLY.

I hadn’t been able to respond with an example because business needs got in the way. We were preparing a small demo to show this, but it fell through the cracks. That said, this is exactly what we were going to show. It’s a core use case and it’s broken. It worked before. We’re stuck on an old version of Directus because of this.

@andrekr thanks for showing this!

On Sep 19, 2023, at 4:08 PM, AndreKR @.***> wrote:

By the way, this issue also affects the REST API and the UI:

https://user-images.githubusercontent.com/1188538/269095540-c34bb7e0-2949-4893-9567-a8fefc1f1400.png — Reply to this email directly, view it on GitHub https://github.com/directus/directus/issues/18863#issuecomment-1726468255, or unsubscribe https://github.com/notifications/unsubscribe-auth/AACCRD4MVLVFPRG52BPUKRDX3ICVVANCNFSM6AAAAAAZCXSKBQ. You are receiving this because you were mentioned.

[AndreKR]

@cwalton Oh wow, just as you say, it does actually work in 9.21.2. Do you happen to know if that is the latest version that works?

[DanielBiegler]

I tried it out personally and your described behavior is reproducible, I get no results. :+1:

this issue also affects the REST API and the UI

Yes, they're the same thing. The UI uses the REST API :smile:

[moneymc]

Any update on this? I tried to install 9.21.2, but it still doesn't work for me.

I'm trying to filter with related tables like this:

let test = { _and: [ { parent_id: { attrs: { attributes_id: { _eq: 10 } } } }, { parent_id: { attrs: { attributes_id: { _eq: 11 } } } } ] }

And I get 0 results.

[mvnxt]

I am facing the same problem and would love to get a solution or workaround for this issue.

[sandros94]

I'm noticing something similar, but not fully have a reproducible way tho. Currently for:

_or: [
  { id: { _eq: '...' } },
  {
    _and: [
      { active: { _eq: true } },
      {
        _and: [
          { table: { _neq: guest.value!.table } },
          { name: { _neq: guest.value!.name } }
        ]
      }
    ]
  }
]

The second _and is treated as an _or, instantly filtering for any table instead of a particular combination of table+name

[Dominic-Marcelino]

Is this something that will be added? Alternatively I could imagine another specific operator like _ain (all_in, name could be optimized 😅 ) that is the same like _inexcept that all conditions must me met

[rijkvanzanten]

@Dominic-Marcelino Do you have a specific example of a query that's currently impossible for you? Just making sure I'm on the same page with what "this" means in "Is this something that will be added?" 🙂 From what I can tell, most if not all examples above are saying effectively "Field X has to be both 1 & 2" which should result in no results, as that's a conflicting statement 🤔

[Dominic-Marcelino]

@rijkvanzanten Thanks for getting back ☺️
In my use-case I'm having a collection products that has a m2m relation to tags. I need to get all products that have the tag with the ID = 1 and ID = 2.

I tried it with the following filter:

{
  "_and": [
    {
      "product_tags": {
        "product_tags_id": {
          "_eq": 23
        }
      }
    },
    {
      "product_tags": {
        "product_tags_id": {
          "_eq": 30
        }
      }
    }
  ]
}

[AndreKR]

From what I can tell, most if not all examples above are saying effectively "Field X has to be both 1 & 2" which should result in no results, as that's a conflicting statement 🤔

Only if you ignore the relationship in the query.

The query

  countries( filter: { cities: {_and: [ {name: {_eq: "Rome"}}, {name: {_eq: "Venice"}} ]} } )

would indeed mean "Give me the countries that have a city that is called Rome and Venice at the same time" which would be conflicting and shouldn't return any results.

But

  countries( filter: { _and: [ {cities: {name: {_eq: "Rome"}}}, {cities: {name: {_eq: "Venice"}}} ] } )

can only be reasonably interpreted as "Give me the countries that have a city called Rome and a city called Venice", which is not conflicting at all.

[cwalton]

I agree completely with this. We need the second interpretation, which is not conflicting. Again, this used to work...

On Jan 30, 2024, at 1:54 PM, AndreKR @.***> wrote:

From what I can tell, most if not all examples above are saying effectively "Field X has to be both 1 & 2" which should result in no results, as that's a conflicting statement 🤔

Only if you ignore the relationship in the query.

The query

countries( filter: { cities: {_and: [ {name: {_eq: "Rome"}}, {name: {_eq: "Venice"}} ]} } ) would indeed mean "Give me the countries that have a city that is called Rome and Venice at the same time" which would be conflicting and shouldn't return any results.

But

countries( filter: { _and: [ {cities: {name: {_eq: "Rome"}}}, {cities: {name: {_eq: "Venice"}}} ] } ) can only be reasonably interpreted as "Give me the countries that have a city called Rome and a city called Venice", which is not conflicting at all.

— Reply to this email directly, view it on GitHub https://github.com/directus/directus/issues/18863#issuecomment-1917783752, or unsubscribe https://github.com/notifications/unsubscribe-auth/AACCRDZ2XUH6EQN6GXH7CUTYRFFYJAVCNFSM6AAAAAAZCXSKBSVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTSMJXG44DGNZVGI. You are receiving this because you were mentioned.

[rijkvanzanten]

Thanks for the clarifications! The fact that this is specifically targeting root-filters targeting one-to-many relationships was the context I was missing before 🙂

[lpkobamn]

Hi! Any updates?