Add option to sort orchestration query results in descending order

[cgillum]

There has been an ask to support querying for orchestration instances and sorting by descending order. We should investigate if this is practical at the storage provider level and, if it is, expose it all the way up to the Java client SDK.

Suggested implementation

• [ ] Update DurableTask.Core's OrchestrationQuery class to expose a new property named SortOrder, which is an enum with possible values of Unspecified (the default), Ascending, or Descending.
• [ ] Update DurableTask.AzureStorage's OrchestrationInstanceStatusQueryCondition class to include this property and update its ToTableQuery method to sort the results accordingly.
• [ ] Add the sort order field to the protobuf definition
• [ ] Update the Durable extension code to populate the corresponding fields when delegating query gRPC requests to the backend storage provider.
• [ ] Update the Java SDK's OrchestrationStatusQuery class with a setSortOrder(...) method.
• [ ] Open issues in microsoft/durabletask-netherite and microsoft/durabletask-mssql to support this functionality (I recommend adding a help-wanted or similar label to these issues).
• [ ] Open an issue in microsoft/durabletask-dotnet to also support this functionality.

[kaibocai]

I wonder what the cx want to sort the query results by, do they want to sort by orchestration completed time?

[ChrisRomp]

@kaibocai In this instance they are happy with the previous change to get instances by ID using the prefix query (https://github.com/Azure/azure-functions-durable-extension/pull/2194) and they are using a Unix timestamp for the ID, but they'd prefer to get them in reverse order, e.g., using a descending Boolean operator or similar. As a workaround they are considering using something like (Int.Max - $timestamp) as the value so they're reverse timestamp even in ascending order. If they can ask for the results in descending order, this wouldn't be necessary.

[kaibocai]

Hi @ChrisRomp, thanks for the info. So basically we just need to provide an option to reverse the query results right, not fancy multiple sort options. Thanks.

[ChrisRomp]

@kaibocai That's all anyone is asking for - for now. 😉 Thank you!!

[cgillum]

After some discussion, we decided to abandon this work item. The primary issue is that Azure Storage (the most common storage backend) doesn't support ordering the results of a query, making it impossible to implement this feature efficiently.