Can not use multiple filters/deeps

[farhaan-shaikh]

Describe the Bug

Making a REST request with 2 or more filters/deeps on a datamodel having "many-to-many" relation fails.
Using a single filter/deep works perfectly fine.

To Reproduce

• Create the following collections with many-to-many relation (between customers and products in this case)
  

• add a few products to each of the customers

• make a rest call with filters on the many-to-many field
  for e.g.

filter[products][products_id][name][_eq]=fan  
filter[products][products_id][price][_eq]=15000

Errors Shown

{
    "errors": [
        {
            "message": "select \"customers\".\"id\", \"customers\".\"name\", \"customers\".\"age\" from \"customers\" where \"customers\".\"id\" in (select \"customers_products\".\"customers_id\" as \"customers_id\" from \"customers_products\" where \"customers_products\".\"customers_id\" is not null and \"customers_products\".\"products_id\" = $1) order by \"customers\".\"id\" asc limit $2 - invalid input syntax for type integer: \"{\"NaN\",\"NaN\"}\"",
            "extensions": {
                "code": "INTERNAL_SERVER_ERROR"
            }
        }
    ]
}

What version of Directus are you using?

9.10.0

What version of Node.js are you using?

16.15.0

What database are you using?

Postgres 13.5

What browser are you using?

postman

How are you deploying Directus?

running locally

[azrikahar]

This technically should be resolved in #8909, but it does seems like currently it's malformed.

• When using filter[products][products_id][name][_eq]=Product C&filter[products][products_id][price][_eq]=100, it's malformed.

  formed filter in the API side:

  {
     "products": {
         "products_id": {
             "_and": [
                 {
                     "name": {
                         "_eq": "Product C"
                     }
                 },
                 {
                     "price": {
                         "_eq": "100"
                     }
                 }
             ]
         }
     }
  }

• When using filter={"_and":[{"products":{"products_id":{"name":{"_eq":"Product C"}}}},{"products":{"products_id":{"price":{"_eq":"100"}}}}]}, it works as intended.

  formed filter in the API side:

  {
     "_and": [
         {
             "products": {
                 "products_id": {
                     "name": {
                         "_contains": "Product C"
                     }
                 }
             }
         },
         {
             "products": {
                 "products_id": {
                     "price": {
                         "_eq": "100"
                     }
                 }
             }
         }
     ]
  }

Thanks for reporting this. In the meantime, you can opt to use the json syntax to ensure it works properly 👍

[farhaan-shaikh]

Thank you for your quick response. The Json syntax works for us :)

[farhaan-shaikh]

The and syntax works for filters but it does not work for deep.
the following syntax:
deep={"_and":[{"products":{"_filter":{"products_id":{"name":{"_eq":"fan"}}}}},{"products":{"_filter":{"products_id":{"price":{"_eq":70}}}}}]}

gives the error:

{
    "errors": [
        {
            "message": "undefined is not iterable (cannot read property Symbol(Symbol.iterator))",
            "extensions": {
                "code": "INTERNAL_SERVER_ERROR"
            }
        }
    ]
}

[azrikahar]

The and syntax works for filters but it does not work for deep.

You should use any of the query parameters inside deep. You were using the filter's value directly inside deep (and also nesting _filter inside which should not be the case), but we need to specify the relational field to apply the deep query parameters to.

So if the relational field is called relational_field, it should be like this: deep={"relational_field":{"_filter":{"_and":[{"products":{"products_id":{"name":{"_eq":"fan"}}}},{"products":{"products_id":{"price":{"_eq":70}}}}]}}}. Also do note that any query params within deep will also need to be prefixed with an underscore, but seems like you already did, just not in the correct position 👍

[eXsiLe95]

• When using filter[products][products_id][name][_eq]=Product C&filter[products][products_id][price][_eq]=100, it's malformed.
  formed filter in the API side:

  {
     "products": {
         "products_id": {
             "_and": [
                 {
                     "name": {
                         "_eq": "Product C"
                     }
                 },
                 {
                     "price": {
                         "_eq": "100"
                     }
                 }
             ]
         }
     }
  }

@azrikahar is it possible to form just this filter, with a nested _and? I'm struggeling with this one for quite some time now. Directus will return an item even though the filter does not apply... I can provide a minimal example if needed but I'm not sure whether it would be better to create another issue for this.

[azrikahar]

s it possible to form just this filter, with a nested _and?

Would you mind explaining what is "this filter" here? That is an example of a malformed filter that should not work 🤔

if you meant nested _and as in the _and is inside a _and/_or, then that should still work. It's just that it is not possible to use them inside fields. This was clarified in #13327 as well 👍

[eXsiLe95]

Thanks for clarifying, I was trying exactly the malformed filter and hoped it would work. Are there any plans to allow nesting _and and _or inside of fields? I have a customer with a use case for this and cannot wrap my head around a workaround. In a related item, two conditions need to be fulfilled, but with the current filter abilities, the filter will also return if only one of the conditions are fulfilled.

[azrikahar]

Are there any plans to allow nesting _and and _or inside of fields?

Not as of now. The most recent related discussion would be #13203, but you can also open a more specific feature request if you'd prefer to.

In a related item, two conditions need to be fulfilled, but with the current filter abilities, the filter will also return if only one of the conditions are fulfilled.

It should still be possible to use _and/_or to achieve that, unless I'm misunderstanding here. Also note that you may need to use deep depending on the exact filtering you're looking for.

[eXsiLe95]

Using this example I'm trying to establish a filter that returns every customer which has a product that is called "butter" and has a price "1". So naturally I would filter something like this:

{
   "_and": [
       {
            "products_id": {
                "name": {
                    "_contains": "Product C"
                }
            }
       },
       {
            "products_id": {
                "price": {
                    "_eq": "100"
                }
            }
       }
   ]
}

But if the customer has one product that is not "butter" but has a price of "1" and the customer has another product "butter" but it is more/less expensive (price !=== "1"), it will still return the customer. To establish this, all I can think of is a query like this:

{
   "products_id": {
      "_and": [
         {
            "name": {
               "_contains": "Product C"
            }
         },
         {
            "price": {
               "_eq": "100"
            }
         }
      ]
   }
}

But I may be missing something...

[azrikahar]

I think it is technically working as intended (I could be wrong!) in the sense that they are indeed customers with "butter" and price "1" (once again not the type of "and" we're looking for here), hence the nesting/level matters like you mentioned.

This isn't the exact desired result, but we can at least ensure the "products" m2m field returns empty array when we use deep as I suspected:

So we can then do an array filter based on that. Understandably that's still not ideal, however I myself am not 100% if it's fully possible as well.

[Pachat]

It looks likes this bug is not resolved in 9.21

The tables:

Project
- id

Stage
- id
- stage (name of the stage)
- network (refers to Network.id)

Network
- id
- network (name of the network)

Project <= M2M => Stage
Stage <= M2O - Network

The following filter on a preset on table Project

{
	"_and": [
		{
			"stage": {
				"stage_id": {
					"stage": {
						"_contains":"COM"
					}
				}
			}
		},
		{
			"stage": {
				"stage_id": {
					"network": {
						"network": { 
							"_contains":"ABC"
						}
					}
				}
			}
		}
	]
}

filters as if OR is applied instead of AND.

[rijkvanzanten]

Linear: ENG-256