A retrieve with constrained sorting

Let me explain my problem with an example. Imagine a data model with the object Car and Colour. Car holds a reference set to colour, so a Car can have multiple colours. Let's imagine a data content of Car A with colours Blue and Yellow and Car B with colours Red and Yellow. Now I want a retrieve in a microflow of all Cars that has the colour Red or Yellow ordered by the related colour. So the retrieve could look like this (in XPath): //Domainmodel.Car[Car_Color/Color[name='Yellow' or name = 'Red']] I add sorting on the association Car_Color/Color/name. Now I expect as a result a list with first Car B (because Red comes before Yellow in alphabet, and Car A has no Red). But that is not the list that I get. I get first object A. I think it's caused by the other joined colour for Car A, namely: Blue. And blue comes before Red. So it seems that for the sorting the framework make separate joins instead of using the join from my constant. Maybe there are reasons for this behaviour I didn't see, but wouldn't it be nice if the framework use the joins made by the constraint for sorting if possible? Let me know if you have suggestions to do this.
2 answers

No, in XPath this is not possible yet. You can do two things:

  • Do a feature request to add support for OQL to retrieve MendixObject instances.
  • Retrieve a DataSet with id's of the Cars via OQL within a Java action with the correct sort order and add the id's to one List. Then retrieve the full instances of Cars with Core.retrieveIdList(..).

I don't think this can be expressed in SQL, so you have to do the sorting manually.