0

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.

|improve this question
  • Please always include any error message verbatim. – Adrian Larson♦ Jan 26 '18 at 22:32
2

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.

|improve this answer
  • 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.