More advanced filter combinators for PaginatedList queries

[michaelbromley]

Is your feature request related to a problem? Please describe.
Currently, all queries implementing the PaginatedList interface and using the ListQueryBuilder get filtering capabilities automatically.

It is possible to specify multiple properties to filter by, and then specify a boolean AND or OR operator across all the filters.

For example:

query {
  productVariants(options: {
    filters: {
      name: { contains: "foo" }
      sku: { contains: "123" }
    },
    filterOperator: OR
  }) {
    items {
      id
       #...
    }
  }
}

This query will return all product variant where the name contains "foo" or the sku contains "123".

We could then set filterOperator: AND to get only those variants where both filters are true.

This is pretty flexible but there are a class of queries which are simply not possible with the global filterOperator approach.

A real-world query I want to be able to do, and currently cannot, is to be able to search through variants of a given product:

query GetVariantsForProduct($searchTerm: String, $productId: ID!) {
  productVariants(options: {
    filters: {
      name: { contains: $searchTerm }
      sku: { contains: "$searchTerm }
      productId: { eq: $productId },
    },
    filterOperator: ??  # <------- no good option here
  }) {
    items {
      id
       #...
    }
  }
}

The logic I want to express is "return all variants where the $searchTerm appears in the name or the sku, AND the productId equals $productId.

Describe the solution you'd like

We need to extend the PaginatedList input object to be able to have this kind of grouped boolean logic.

This extension should be done in a backward-compatible way, because the existing API is perfectly fine for ~90% of use-cases, and quite intuitive in its simplicity.

Prior Art

We have addressed something similar in the SearchInput where we allow such groupings when filtering by facet value id.

Here's how it looks:

The facetValueFilters input can be used to specify multiple filters, combining each with either and or or.

For example, to filter by both the "Camera" and "Nikkon" facet values, we would use:

{
  "facetValueFilters": [
    { "and": "9" },
    { "and": "11" }
  ]
}

To filter by "Nikkon" or "Sony", we would use:

{
  "facetValueFilters": [
    { "or": ["11", "15"] }
  ]
}

Directus support a tree-like operator structure which allows complex nesting:

{
  "_or": [
    {
      "_and": [
        {
          "user_created": {
            "_eq": "$CURRENT_USER"
          }
        },
        {
          "status": {
            "_in": [
              "published",
              "draft"
            ]
          }
        }
      ]
    },
    {
      "_and": [
        {
          "user_created": {
            "_neq": "$CURRENT_USER"
          }
        },
        {
          "status": {
            "_in": [
              "published"
            ]
          }
        }
      ]
    }
  ]
}

The Directus approach is certainly more powerful, and reminds me of the Elasticsearch API. It seems like Directus supports arbitrary levels of sub-nesting. Not sure we'd need that - depends on the complexity of the implementation.

I will investigate what it would take to implement something like this.