I want to build a query to order result based on Child field like this:

select id, name, other_field, (select id, name, other_field from Child__r) from Parent order by Child__r.name

But, I am getting an error message. I've searched Salesforce documentations, but did not find anything.

Edit: ERROR message added below:

INVALID_FIELD: order by Child__r.name ^ ERROR at Row:1:Column:254

Didn't understand relationship 'Child__r' in field path. If you are attempting to use a custom relationship, be sure to append the '__r' after the custom relationship name. Please reference your WSDL or the describe call for the appropriate names.

closed as unclear what you're asking by Raul, glls, Ratan Paul, battery.cord, Himanshu Jan 29 '18 at 2:58

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • Please always include any error message verbatim. – Adrian Larson Jan 26 '18 at 22:32

You can query the child object instead and pull in the parent fields using a Right Inner Join:

SELECT Id, Name, SomeField__c, Parent__c, Parent__r.Name, Parent__r.OtherField__c
FROM Child__c ORDER BY Name

But no, you cannot sort the parent records by child values, due to the nature of a many-to-one relationship. If you have one parent with children Alice and David, and another with children Bob and Charlie, you need a way to determine which would come first. With number fields you might order by Max, Min, Avg, etc. But that doesn't make sense with text fields.

  • That is an alternative, but I want to return Parent object. – Kamruzzaman Jan 26 '18 at 22:40
  • 1
    You realize it's a many-to-one relationship, right? There will be more than one name. – Adrian Larson Jan 26 '18 at 22:52
  • Yes, child-to-parent its many-to-one relationship. – Kamruzzaman Jan 26 '18 at 22:56
  • One concern.. If it's a one-to-one relationship, would be possible to return Parent object? – Kamruzzaman Jan 27 '18 at 19:33
  • There is no such thing. – Adrian Larson Jan 27 '18 at 19:50

Not the answer you're looking for? Browse other questions tagged or ask your own question.