[Bug]: Nested filter on Self-Referencing Relationships returns incorrect results

[magnh]

What happened?

Problem

When using GraphQL nested filters on self-referencing relationships (e.g., parent/child hierarchy), the filter returns incorrect results.

This query gives incorrect results

query {
  books(filter: { category: { parent: { name: { contains: "Classic" } } } }) {
    items {
      id
      category {
        name
        parent {
          name
        }
      }
    }
  }
}

Expected Behavior

Returns book items with categories whose parent's name contains "Classic".

Proposed Solution

1. In HandleNestedFilterForSql, use AddJoinPredicatesForRelationship instead of AddJoinPredicatesForRelatedEntity
2. Create an EntityRelationshipKey using the relationship name (filter field name) to look up the correct FK definition
3. Call the method on the parent query structure (not the EXISTS subquery) with the correct parameters:
   • fkLookupKey: {queryStructure.EntityName, filterField.Name}
   • targetEntityName: the nested filter entity name
   • subqueryTargetTableAlias: the EXISTS subquery's source alias

In BaseGraphQLFilterParsers.cs:

/// <summary>
/// For SQL, a nested filter represents an EXISTS clause with a join between
/// the parent entity being filtered and the related entity representing the
/// non-scalar filter input. This function:
/// 1. Defines the Exists Query structure
/// 2. Recursively parses any more(possibly nested) filters on the Exists sub query.
/// 3. Adds join predicates between the related entities to the Exists sub query.
/// 4. Adds the Exists subquery to the existing list of predicates.
/// </summary>
/// <param name="ctx">The middleware context</param>
/// <param name="filterField">The nested filter field.</param>
/// <param name="subfields">The subfields of the nested filter.</param>
/// <param name="predicates">The predicates parsed so far.</param>
/// <param name="queryStructure">The query structure of the entity being filtered.</param>
/// <exception cref="DataApiBuilderException">
/// throws if a relationship directive is not found on the nested filter input</exception>
private void HandleNestedFilterForSql(
    IMiddlewareContext ctx,
    InputField filterField,
    List<ObjectFieldNode> subfields,
    List<PredicateOperand> predicates,
    BaseQueryStructure queryStructure,
    ISqlMetadataProvider metadataProvider)
{
    string? targetGraphQLTypeNameForFilter = RelationshipDirectiveType.GetTarget(filterField);

    if (targetGraphQLTypeNameForFilter is null)
    {
        throw new DataApiBuilderException(
            message: "The GraphQL schema is missing the relationship directive on input field.",
            statusCode: HttpStatusCode.InternalServerError,
            subStatusCode: DataApiBuilderException.SubStatusCodes.UnexpectedError);
    }

    string nestedFilterEntityName = metadataProvider.GetEntityName(targetGraphQLTypeNameForFilter);

    // Validate that the field referenced in the nested input filter can be accessed.
    bool entityAccessPermitted = queryStructure.AuthorizationResolver.AreRoleAndOperationDefinedForEntity(
        entityIdentifier: nestedFilterEntityName,
        roleName: GetHttpContextFromMiddlewareContext(ctx).Request.Headers[CLIENT_ROLE_HEADER].ToString(),
        operation: EntityActionOperation.Read);

    if (!entityAccessPermitted)
    {
        throw new DataApiBuilderException(
            message: DataApiBuilderException.GRAPHQL_FILTER_ENTITY_AUTHZ_FAILURE,
            statusCode: HttpStatusCode.Forbidden,
            subStatusCode: DataApiBuilderException.SubStatusCodes.AuthorizationCheckFailed);
    }

    List<Predicate> predicatesForExistsQuery = new();

    // Create an SqlExistsQueryStructure as the predicate operand of Exists predicate
    // This query structure has no order by, no limit and selects 1
    // its predicates are obtained from recursively parsing the nested filter
    // and an additional predicate to reflect the join between main query and this exists subquery.
    SqlExistsQueryStructure existsQuery = new(
        GetHttpContextFromMiddlewareContext(ctx),
        metadataProvider,
        queryStructure.AuthorizationResolver,
        this,
        predicatesForExistsQuery,
        nestedFilterEntityName,
        queryStructure.Counter);

    // Recursively parse and obtain the predicates for the Exists clause subquery
    Predicate existsQueryFilterPredicate = Parse(ctx,
            filterField,
            subfields,
            existsQuery);
    predicatesForExistsQuery.Push(existsQueryFilterPredicate);

    // Add JoinPredicates to the subquery query structure so a predicate connecting
    // the outer table is added to the where clause of subquery.
    // For self-referencing relationships (e.g., parent/child hierarchy), we need to use
    // the relationship name to look up the correct foreign key definition.
    // The parent query (queryStructure) calls AddJoinPredicatesForRelationship which adds
    // predicates to the subquery (existsQuery), connecting queryStructure.SourceAlias to existsQuery.SourceAlias.
    string relationshipName = filterField.Name;
    EntityRelationshipKey fkLookupKey = new(queryStructure.EntityName, relationshipName);
    BaseSqlQueryStructure sqlQueryStructure = (BaseSqlQueryStructure)queryStructure;
    sqlQueryStructure.AddJoinPredicatesForRelationship(
        fkLookupKey: fkLookupKey,
        targetEntityName: nestedFilterEntityName,
        subqueryTargetTableAlias: existsQuery.SourceAlias,
        subQuery: existsQuery);

    // The right operand is the SqlExistsQueryStructure.
    PredicateOperand right = new(existsQuery);

    // Create a new unary Exists Predicate
    Predicate existsPredicate = new(left: null, PredicateOperation.EXISTS, right);

    // Add it to the rest of the existing predicates.
    predicates.Push(new PredicateOperand(existsPredicate));

    // Add all parameters from the exists subquery to the main queryStructure.
    foreach ((string key, DbConnectionParam value) in existsQuery.Parameters)
    {
        queryStructure.Parameters.Add(key, value);
    }
}

/// <summary>
/// Helper method to get the HttpContext from the MiddlewareContext.
/// </summary>
/// <param name="ctx">Middleware context for the object.</param>
/// <returns>HttpContext</returns>
/// <exception cref="DataApiBuilderException">throws exception when http context could not be found.</exception>
public HttpContext GetHttpContextFromMiddlewareContext(IMiddlewareContext ctx)
{
    // Get HttpContext from IMiddlewareContext and fail if resolved value is null.
    if (!ctx.ContextData.TryGetValue(nameof(HttpContext), out object? httpContextValue))
    {
        throw new DataApiBuilderException(
            message: "No HttpContext found in GraphQL Middleware Context.",
            statusCode: HttpStatusCode.BadRequest,
            subStatusCode: DataApiBuilderException.SubStatusCodes.BadRequest);
    }

    return (HttpContext)httpContextValue!;
}

Version

Latest (main)

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

N/A

Code of Conduct

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