Retrieve by association with filtering and sorting to improve performance and maintainability

Retrieving over association is often needed as some changes may not be committed yet (dirty state). Sometimes, when you retrieve a list and you only need a part of the list, a filter activity is required. Next, if you also need to sort the list, another sort activity is required.

Having this sequence of microflow activities has a couple of downsides:

1. It performs a database retrieve of the full list, while you could already filter on database level applying an XPath constraint (performance)
2. It requires to sort using the CPU of the app instead of doing some of the heavy lifting by the database (performance)
3. It introduces extra variables in your microflow which you probably don't need and even may be improperly used accidentally (maintainability)

It would be great if the "retrieve over association" activity would also provide options to:

• Filter using simple XPath contraints (like retrieve from database in nanoflows)
  • Simple, because complex filtering would not be possible on the cached items and
  • XPath constraints, because filtering by expression would not be possible on the database items
• Sort on multiple attributes (like retrieve from database)

This would make a "retrieve over association" in a microflow look very similar to the "retrieve from database" currently available in nanoflows:

Which is limited compared with the full-blown options in a "retrieve from database" activity in a microflow (mind the associations):

This idea is very similar to this one, but I tried to elaborate a bit more on the how and why, added sorting and tried to bring a more realistic solution.