Relationship query on Custom Metadata Type giving wrong results

Question

I have a parent Metadata Type having a child relationship. SOQL behaves differently in each of the following scenarios:

SELECT ALLFIELDS, (SELECT ALLFIELDS FROM Children__r ORDER BY Order__c) FROM Parent__mdt

• Returns: Incorrect children (some are missing)

SELECT ALLFIELDS, (SELECT ALLFIELDS FROM Children__r) FROM Parent__mdt

• Returns: Correct children

SELECT ALLFIELDS, (SELECT ALLFIELDS FROM Children__r ORDER BY Order__c) FROM Parent__mdt WHERE DeveloperName = 'MyName'

• Returns: Correct children

SELECT ALLFIELDS, (SELECT Id, DeveloperName FROM Children__r ORDER BY Order__c) FROM Parent__mdt

• Returns: Correct children but wrong order!

I have also tried using SOQL for-loop as suggested by sfdcfox but no luck:

for (Parent__mdt p : [SELECT ALLFIELDS, (SELECT ALLFIELDS FROM Children__r ORDER BY Order__c) FROM Parent__mdt])
{
    Child__mdt[] childList = new Child__mdt[]{};
    for (Child__mdt c : p.Children__r) childList.add(c);
    System.debug(childList.size()); // wrong
}

Btw ALLFIELDS is a placeholder for all fields on that object.

This seems to be related to this KB issue, but I could not find the exact problem in the KB. Anybody has any solutions? I want to be able to include all fields in my query and I want to return all records.

Answer 1

This appears to be a Salesforce bug. What appears to be causing the issue is having an ORDER BY inside one of the sub-queries.

As a workaround to the bug you can remove the inner ORDER BY and that will allow you to query for all fields and retrieve the data correctly.

To make up for the fact that your query won't be correctly sorted anymore, you can implement custom sorting by wrapping the records inside of an Apex class that implements the Comparable interface: https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/apex_list_sorting_sobject.htm