[Bug]: orderBy clause has support for OR/AND operator and relationship fields which do not work

[vadeveka]

What happened?

OrderBy clause for the queries has support for OR and AND operators per schema.

input GeographyOrderByInput {
  GeographyID: OrderBy
  ...
  State: OrderBy
  Country: OrderBy
  ZipCode: OrderBy
  and: [GeographyOrderByInput]
  or: [GeographyOrderByInput]
}

However, using it in a query fails with an exception.
Sample query

query {
  geographies(orderBy: { or: { GeographyID: ASC, State: ASC } }) {
    items {
      GeographyID
    }
  }
}

Response in development mode:

{
  "errors": [
    {
      "message": "Mapped fieldname could not be found.",
      "extensions": {
        "message": "Mapped fieldname could not be found.",
        "stackTrace": "   at Azure.DataApiBuilder.Core.Resolvers.SqlQueryStructure.ProcessGqlOrderByArg(List`1 orderByFields, IInputField orderByArgumentSchema) in Q:\\Repos\\data-api-builder\\src\\Core\\Resolvers\\Sql Query Structures\\SqlQueryStructure.cs:line 868\r\n   at Azure.DataApiBuilder.Core.Resolvers.SqlQueryStructure..ctor(IMiddlewareContext ctx, IDictionary`2 queryParams, ISqlMetadataProvider sqlMetadataProvider, IAuthorizationResolver authorizationResolver, IObjectField schemaField, FieldNode queryField, IncrementingInteger counter, RuntimeConfigProvider runtimeConfigProvider, GQLFilterParser gQLFilterParser, String entityName) in Q:\\Repos\\data-api-builder\\src\\Core\\Resolvers\\Sql Query Structures\\SqlQueryStructure.cs:line 491\r\n   at Azure.DataApiBuilder.Core.Resolvers.SqlQueryStructure..ctor(IMiddlewareContext ctx, IDictionary`2 queryParams, ISqlMetadataProvider sqlMetadataProvider, IAuthorizationResolver authorizationResolver, RuntimeConfigProvider runtimeConfigProvider, GQLFilterParser gQLFilterParser) in Q:\\Repos\\data-api-builder\\src\\Core\\Resolvers\\Sql Query Structures\\SqlQueryStructure.cs:line 104\r\n   at Azure.DataApiBuilder.Core.Resolvers.SqlQueryEngine.ExecuteAsync(IMiddlewareContext context, IDictionary`2 parameters, String dataSourceName) in Q:\\Repos\\data-api-builder\\src\\Core\\Resolvers\\SqlQueryEngine.cs:line 72\r\n   at Azure.DataApiBuilder.Service.Services.ExecutionHelper.ExecuteQueryAsync(IMiddlewareContext context) in Q:\\Repos\\data-api-builder\\src\\Core\\Services\\ExecutionHelper.cs:line 79\r\n   at ResolverTypeInterceptor.<>c__DisplayClass5_1.<<-ctor>b__5>d.MoveNext() in Q:\\Repos\\data-api-builder\\src\\Core\\Services\\ResolverTypeInterceptor.cs:line 23\r\n--- End of stack trace from previous location ---\r\n   at HotChocolate.Execution.Processing.Tasks.ResolverTask.ExecuteResolverPipelineAsync(CancellationToken cancellationToken)\r\n   at HotChocolate.Execution.Processing.Tasks.ResolverTask.TryExecuteAsync(CancellationToken cancellationToken)",
        "code": "UnexpectedError"
      }
    }
  ]
}

Similar failure when a relationship is created from a type - the schema shows relationship field available for ordering but does not work when used in query

In below example, Customer type had a 1:M relationship with salesOrderHeader. Below is a snippet of orderBy clause generated.

"""Order by input for Customer GraphQL type"""
input CustomerOrderByInput {
  """Order by options for CustomerID"""
  CustomerID: OrderBy
 
  ....
 
  """Order by options for salesOrderHeader"""
  salesOrderHeader: SalesOrderHeaderOrderByInput
 
  """Conditions to be treated as AND operations"""
  and: [CustomerOrderByInput]
 
  """Conditions to be treated as OR operations"""
  or: [CustomerOrderByInput]
}

Sample query that fails

 
query {
  customers (first: 5 orderBy:  {salesOrderHeader:  {
     SalesOrderID: DESC
  }
  }){
    items {
        CustomerID
        FirstName
        LastName
        EmailAddress
        salesOrderHeader {
            items {
                SalesOrderID
                OrderDate
            }
        }
    }
  }
  }

Version

latest

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

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

[ehalsey]

Wondering if this is the same or a different issue. Here's my query:

query Appointments {
    appointments {
        items {
            appointmentSlots(orderBy: { providerAvailableSlots: { SlotStart: ASC } }) {
                items {
                    providerAvailableSlots {
                        SlotStart
                        SlotEnd
                    }
                }
            }
        }
    }
}

    "errors": [
        {
            "message": "Mapped fieldname could not be found.",
            "extensions": {
                "code": "UnexpectedError"
            }
        }
    ]

The goal is to sort the providerAvailable slots results by SlotStart so I don't have to do this on the client.

Running with out the orderBy works as expected:

query Appointments {
    appointments(first: 1) {
        items {
            appointmentSlots {
                items {
                    providerAvailableSlots {
                        SlotStart
                        SlotEnd
                    }
                }
            }
        }
    }
}

{
    "data": {
        "appointments": {
            "items": [
                {
                    "appointmentSlots": {
                        "items": [
                            {
                                "providerAvailableSlots": {
                                    "SlotStart": "2025-01-31T12:45:00.000Z",
                                    "SlotEnd": "2025-01-31T13:00:00.000Z"
                                }
                            },
                            {
                                "providerAvailableSlots": {
                                    "SlotStart": "2025-01-31T12:30:00.000Z",
                                    "SlotEnd": "2025-01-31T12:45:00.000Z"
                                }
                            }
                        ]
                    }
                }
            ]
        }
    }
}