+ Start a Discussion
TLFTLF 

What is the default sort order of a child relationship?

I have two custom objects that represent an order with line items. Order is the master in a master/detail relationship, and OrderItem is the detail. I'm seeing no rhyme, nor reason to the default order in which OrderItems are returned in a relationship query. Here is a little code snippet and resulting debug output that shows what I mean:

 

 

Anonymous execution was successful.

19.0 APEX_CODE,DEBUG;APEX_PROFILING,INFO;CALLOUT,INFO;DB,INFO;VALIDATION,INFO;WORKFLOW,INFO
Execute Anonymous: Order__c o = [SELECT Id, (SELECT Id, Name, CreatedDate, LastModifiedDate FROM OrderItems__r) FROM Order__c WHERE Id = 'a0880000008hBdKAAU'];
Execute Anonymous: for (OrderItem__c item : o.OrderItems__r) {
Execute Anonymous: System.debug(item);
Execute Anonymous: }
Execute Anonymous:
Execute Anonymous:
13:15:22.454|EXECUTION_STARTED
13:15:22.454|CODE_UNIT_STARTED|[EXTERNAL]|execute_anonymous_apex
13:15:22.454|SOQL_EXECUTE_BEGIN|[1,20]|Aggregations:1|SELECT Id, (SELECT Id, Name, CreatedDate, LastModifiedDate FROM OrderItems__r) FROM Order__c WHERE Id = 'a0880000008hBdKAAU'
13:15:22.471|SOQL_EXECUTE_END|[1,20]|Rows:1
13:15:22.471|METHOD_ENTRY|[3,5]|System.debug(SOBJECT:OrderItem__c)
13:15:22.472|USER_DEBUG|[3,5]|DEBUG|OrderItem__c:{Order__c=a0880000008hBdKAAU, Name=ITEM-000010782, CreatedDate=2010-09-01 12:39:58, LastModifiedDate=2010-09-02 17:13:39, Id=a028000000CY0LzAAL}
13:15:22.472|METHOD_EXIT|[3,5]|System.debug(SOBJECT:OrderItem__c)
13:15:22.472|METHOD_ENTRY|[3,5]|System.debug(SOBJECT:OrderItem__c)
13:15:22.472|USER_DEBUG|[3,5]|DEBUG|OrderItem__c:{Order__c=a0880000008hBdKAAU, Name=ITEM-000010783, CreatedDate=2010-09-01 12:40:41, LastModifiedDate=2010-09-02 17:13:39, Id=a028000000CY0M4AAL}
13:15:22.472|METHOD_EXIT|[3,5]|System.debug(SOBJECT:OrderItem__c)
13:15:22.472|METHOD_ENTRY|[3,5]|System.debug(SOBJECT:OrderItem__c)
13:15:22.473|USER_DEBUG|[3,5]|DEBUG|OrderItem__c:{Order__c=a0880000008hBdKAAU, Name=ITEM-000010784, CreatedDate=2010-09-01 12:40:41, LastModifiedDate=2010-09-02 17:13:39, Id=a028000000CY0M5AAL}
13:15:22.473|METHOD_EXIT|[3,5]|System.debug(SOBJECT:OrderItem__c)
13:15:22.473|METHOD_ENTRY|[3,5]|System.debug(SOBJECT:OrderItem__c)
13:15:22.474|USER_DEBUG|[3,5]|DEBUG|OrderItem__c:{Order__c=a0880000008hBdKAAU, Name=ITEM-000010785, CreatedDate=2010-09-01 12:40:41, LastModifiedDate=2010-09-02 17:13:39, Id=a028000000CY0M6AAL}
13:15:22.474|METHOD_EXIT|[3,5]|System.debug(SOBJECT:OrderItem__c)
13:15:22.474|METHOD_ENTRY|[3,5]|System.debug(SOBJECT:OrderItem__c)
13:15:22.474|USER_DEBUG|[3,5]|DEBUG|OrderItem__c:{Order__c=a0880000008hBdKAAU, Name=ITEM-000010786, CreatedDate=2010-09-01 12:40:41, LastModifiedDate=2010-09-02 17:13:39, Id=a028000000CY0M7AAL}
13:15:22.474|METHOD_EXIT|[3,5]|System.debug(SOBJECT:OrderItem__c)
13:15:22.474|METHOD_ENTRY|[3,5]|System.debug(SOBJECT:OrderItem__c)
13:15:22.475|USER_DEBUG|[3,5]|DEBUG|OrderItem__c:{Order__c=a0880000008hBdKAAU, Name=ITEM-000010813, CreatedDate=2010-09-02 11:48:19, LastModifiedDate=2010-09-02 17:13:39, Id=a028000000CY0xpAAD}
13:15:22.475|METHOD_EXIT|[3,5]|System.debug(SOBJECT:OrderItem__c)
13:15:22.475|METHOD_ENTRY|[3,5]|System.debug(SOBJECT:OrderItem__c)
13:15:22.475|USER_DEBUG|[3,5]|DEBUG|OrderItem__c:{Order__c=a0880000008hBdKAAU, Name=ITEM-000010788, CreatedDate=2010-09-01 12:40:41, LastModifiedDate=2010-09-02 17:13:39, Id=a028000000CY0M9AAL}
13:15:22.475|METHOD_EXIT|[3,5]|System.debug(SOBJECT:OrderItem__c)
13:15:22.475|METHOD_ENTRY|[3,5]|System.debug(SOBJECT:OrderItem__c)
13:15:22.476|USER_DEBUG|[3,5]|DEBUG|OrderItem__c:{Order__c=a0880000008hBdKAAU, Name=ITEM-000010791, CreatedDate=2010-09-01 20:24:43, LastModifiedDate=2010-09-02 17:15:18, Id=a028000000CY0afAAD}
13:15:22.476|METHOD_EXIT|[3,5]|System.debug(SOBJECT:OrderItem__c)
13:15:22.476|METHOD_ENTRY|[3,5]|System.debug(SOBJECT:OrderItem__c)
13:15:22.477|USER_DEBUG|[3,5]|DEBUG|OrderItem__c:{Order__c=a0880000008hBdKAAU, Name=ITEM-000010792, CreatedDate=2010-09-01 20:25:16, LastModifiedDate=2010-09-02 17:13:39, Id=a028000000CY0akAAD}
13:15:22.477|METHOD_EXIT|[3,5]|System.debug(SOBJECT:OrderItem__c)
13:15:22.477|METHOD_ENTRY|[3,5]|System.debug(SOBJECT:OrderItem__c)
13:15:22.477|USER_DEBUG|[3,5]|DEBUG|OrderItem__c:{Order__c=a0880000008hBdKAAU, Name=ITEM-000010812, CreatedDate=2010-09-02 11:47:38, LastModifiedDate=2010-09-02 17:13:39, Id=a028000000CY0xkAAD}
13:15:22.477|METHOD_EXIT|[3,5]|System.debug(SOBJECT:OrderItem__c)
13:15:22.477|METHOD_ENTRY|[3,5]|System.debug(SOBJECT:OrderItem__c)
13:15:22.478|USER_DEBUG|[3,5]|DEBUG|OrderItem__c:{Order__c=a0880000008hBdKAAU, Name=ITEM-000010787, CreatedDate=2010-09-01 12:40:41, LastModifiedDate=2010-09-02 17:13:39, Id=a028000000CY0M8AAL}
13:15:22.478|METHOD_EXIT|[3,5]|System.debug(SOBJECT:OrderItem__c)

 

When you look closely at the list of OrderItem objects returned they do not seem to be sorted in any particular order. They aren't in order by Name, Id, CreatedDate or LastModifiedDate. I don't have an ORDER BY clause in my relationship query, so I am wondering what the default order is. Any ideas? The reason I'm asking is because I'm using a Visualforce email template to render the child OrderItem objects. Visualforce email templates have their own, internal controller that retrieve the child OrderItems in this default order, and therefore they are rendered in this strange, unexpected order. If I at least knew what order they are sorted in, I could possibly do something at the time of insert to insure they are rendered properly in my email template.

SuperfellSuperfell

There is no default order, it you want them in a particular order, you ned to use an order by clause.

TLFTLF

That's my problem... in a Visualforce email template, you have no control over the query used to retrieve the child objects, because it's an internal controller. I guess I'll have to render the OrderItems in a Visualforce component, with it's own controller (with an appropriate "order by" clause), and then include that component in my email template.