_and filter doesn't work on many to any

[xdoo]

Describe the Bug

I have a collection, that references to other collections via many to any relationship. In my sample I've got a collection "Main", that has a m2a relation to "Foo" and "Bar".

I want to filter all "Mains", that have a Foo "Foo 2" AND that have a Bar "Bar 1". Filtering one of those works, but I'm not able to get a result if I do it with "_and". Combining them with _and results always in an empty array.

Here ist the GraphQL query:

To Reproduce

1. Create the Foo & Bar collections and add to both a Field "Name"
2. Create a Main collection and create a M2A field "References"
3. Create some Foo & Bar Datasets.
4. Ceate some Main Datasets and add some combinations of Foos & Bars as references.
5. Try to filter like in the bug description.

Directus Version

v10.6.3

Hosting Strategy

Self-Hosted (Docker Image)

[br41nslug]

It looks like the filter you've created doesnt actually put the filters in the AND group

happy to re-open the issue if that does not fix it for you 😄

[xdoo]

Thank you @br41nslug for the answer. You're right, so I added the two filters to the _and:

That changed nothing. If I'll delete one of them:

I tried the filter on the GUI only to check if it works there. My main issue is, that it doesn't work in GrapHQL. So I'll reopen this issue.

[br41nslug]

Does it then also work the other way around? like just the "Foo 1" filter because i was not able to reproduce this in the screenshot i originally shared 🤔

[br41nslug]

I think the missing key here is that you'll probably want to use the relational _some or _none filters in combination with the _and because if you'd have 2 items of Foo selected in the m2a a normal _and would never work.

https://docs.directus.io/reference/filter-rules.html#some-vs-none-in-one-to-many

something like perhaps:

filter={
_and: [
  { _some: [ { references: { "item:bar": { _contains: "something" } } }  ] },
  { _some: [ { references: { "item:foo": { _contains: "something else" } } }  ] },
}

[xdoo]

I saw that in the docs, but it didn't work for me (I'll use it, if it works ;).

I think that's pretty near to your sample:

query Main(
    $foo: String, 
    $bar: String
) {
    Main (filter: {
            _and: [
                { _some: [{ References: { BarName: { _contains: $bar }}}]}
                { _some: [{ References: { FooName: {_contains: $foo}}}]}
            ]   
    }) {
        id
    }
}

What I get is this:

{
    "errors": [
        {
            "message": "GraphQL validation error.",
            "extensions": {
                "code": "GRAPHQL_VALIDATION",
                "errors": [
                    {
                        "message": "Field \"_some\" is not defined by type \"Main_filter\". Did you mean \"_or\"?",
                        "locations": [
                            {
                                "line": 7,
                                "column": 19
                            }
                        ]
                    },
                    {
                        "message": "Field \"_some\" is not defined by type \"Main_filter\". Did you mean \"_or\"?",
                        "locations": [
                            {
                                "line": 8,
                                "column": 19
                            }
                        ]
                    }
                ]
            }
        }
    ]
}

I think there should be a way to filter for different values / collection in a M2A relationship. You can do this type of thing in SQL as well, so why not in a filter?

I'm thankful for every idea 😊

[xdoo]

I can't find any _some types in the GraphQl schema. So I don't think that _some works.

query Main(
    $foo: String, 
    $bar: String
) {
    Main (filter: {
            
        References: {
            _and: [
                {
                    item__Bar: {
                        BarName: { _contains: $bar }
                    }
                }
                {
                    item__Foo: {
                        FooName: { _contains: $foo }
                    }
                }
            ]
            
        }
            
    }) {
        id
    }
} 

That query above should have an implicit "_some". It says (or I want to say): Give me every "Main" item, that references at least one Bar item with name "X" and one Foo item with name "Y".

[xdoo]

@br41nslug what's about this ticket? You closed it without any solution.

This doesn't work - and I think it should (if you don't think so, then this should be in the docs anywhere):

query Main(
    $foo: String, 
    $bar: String
) {
    Main (filter: {
            
        References: {
            _and: [
                {
                    item__Bar: {
                        BarName: { _contains: $bar }
                    }
                }
                {
                    item__Foo: {
                        FooName: { _contains: $foo }
                    }
                }
            ]
            
        }
            
    }) {
        id
    }
} 

If I'm wrong, please show me the solution and I'm very happy.

[paescuj]

The solution basically lies in #20009 (comment), although _some isn't available for GraphQL at the moment (see #14228).