[Bug]: Hierarchical/relationship filtering works correctly in one direction but not in the other direction

[CalvinQuark]

What happened?

Whereas the fix for Issue 825 correctly addresses hierarchical/relationship filtering when the outer entity is on the many side of a one-to-many relationship, the inverse filtering behavior where the outer entity is on the one side of a one-to-many relationship seems incorrect.

The following GraphQL query pertaining to the T-SQL schema, data, and dab-config.json provided in Issue 825 has the outer entity, books, as the many side and the inner entity, series, as the one side of the relationship.

{
  books(filter: { and: [{title: {contains: "m"}}, { series: { id: { eq: 10001 }}}]}) {
    items {
      id
      title
      series {
        id
        name
      }
    }
  }
}

Which yields:

{
  "data": {
    "books": {
      "items": [
        {
          "id": 1015,
          "title": "Endymion",
          "series": {
            "id": 10001,
            "name": "Hyperion Cantos"
          }
        },
        {
          "id": 1016,
          "title": "The Rise of Endymion",
          "series": {
            "id": 10001,
            "name": "Hyperion Cantos"
          }
        }
      ]
    }
  }
}

The foregoing query fetches a properly filtered response based on the conjunction of the two filter predicates.

However, if one inverts the query, with the outer entity, series, the one side of the relationship and the inner entity, books, the many side of the relationship:

{
  series(filter: {and: [{id: {eq: 10001}}, {books: {title: {contains: "m"}}}]} ) {
    items {
      id
      name
      books {
        items {
          id
          title
          year
          pages
        }
      }
    }
  }
}

The result is:

{
  "data": {
    "series": {
      "items": [
        {
          "id": 10001,
          "name": "Hyperion Cantos",
          "books": {
            "items": [
              {
                "id": 1013,
                "title": "Hyperion",
                "year": 1989,
                "pages": 482
              },
              {
                "id": 1014,
                "title": "The Fall of Hyperion",
                "year": 1990,
                "pages": 517
              },
              {
                "id": 1015,
                "title": "Endymion",
                "year": 1996,
                "pages": 441
              },
              {
                "id": 1016,
                "title": "The Rise of Endymion",
                "year": 1997,
                "pages": 579
              }
            ]
          }
        }
      ]
    }
  }
}

This filtering behavior seems incorrect. The query returns all items in the books collection so long as at least one of the items satisfies the filtering predicate (contains 'm'). Should not this query instead return only those items that satisfy the predicate? If this behavior of the API is intended, then it strikes me as both unintuitive and contradictory to the promise of GraphQL to "give clients the power to ask for exactly what they need and nothing more".

Of course, the intended filtered results can be obtained in one-shot if the query is crafted correctly (i.e., in one relationship direction and not its inverse). Nevertheless, this observation breaks down when querying multiple related tables where an interim table is related to two other tables on the one-side of a relationship with one of the tables and on the many side of a relationship with the other table in the query. In that situation, either multiple queries are required, or the query must be encapsulated in a stored procedure, or subsequent client-side filtering must be employed to properly filter the result set.

Version

8.52

What database are you using?

Azure SQL

What hosting model are you using?

Local (including CLI)

Which API approach are you accessing DAB through?

GraphQL

Relevant log output

No response

Code of Conduct

• [X] I agree to follow this project's Code of Conduct

[CalvinQuark]

I'm quite new to working with GraphQL. Consequently, I didn't yet realize when I submitted this issue that one could alternatively craft this query as:

{
  series(filter: {id: {eq: 10001}} ) {
    items {
      id
      name
      books(filter: {title: {contains: "m"}}) {
        items {
          id
          title
          year
          pages
        }
      }
    }
  }
}

This query version filters the response correctly as intended. Nevertheless, the behavior still seems incorrect when the inner filter is instead shifted to the outer entity as explained in the issue submission.

[seantleonard]

Thanks for reporting. We have this on our radar but don't have a timeline that we can share.