SOQL Order By field in child relationship

Question

I'm trying to use SOQL (with JSForce in a nodejs app) to execute a query like what is below.

SELECT Name, Oppnum__c, Id, ( SELECT CreatedDate FROM Times__r ORDER BY CreatedDate DESC LIMIT 1) 
FROM Opportunity 
WHERE Id NOT IN 
(SELECT Opportunity__c 
  FROM Rating__c 
  WHERE FDept__c = 'Mgmt') 
AND Id IN 
(SELECT OppTime__c 
  FROM Time__c 
  WHERE TimeRole__c IN ('MGMT'))

The purpose is to get a list of projects than need to be rated by someone internally, so I'm querying for all the jobs they have NOT rated the (the first where clause) but where they HAVE put time against a job (the second WHERE clause).

The fourth field I'm pulling in from Times__r is an attempt at getting the the data to show up in an order based on the CreatedDate of a child record. It's a one-to-many relationship, so doing it this way returns only the max value, without using MAX (not allowed). The result for that column comes out looking like this:

[{"CreatedDate":"2018-05-25T15:47:48.000+0000"}]

I'd like to figure out how to sort my data based on that timestamp, but I'm stuck now and hoping someone else has had to deal with the same issue. Any ideas?

Answer 1

Seems like a great use case for a rollup.

1. Create a field on Opportunity named something like Most_Recent_Foo__c (obviously change foo to something more meaningful).
2. Install Declarative Rollup Summaries for Lookups (DLRS).
3. Create a Lookup Rollup Summary record.
   • Parent Object - Opportunity
   • Relationship Field - Opportunity__c (?)
   • Child Object - Time__c (?)
   • Field to Aggregate - CreatedDate
   • Aggregate Operation - Last
   • Aggregate Result Field: Most_Recent_Foo__c

Once you've done all of the above, you can query more simply:

SELECT Most_Recent_Foo__c FROM Opportunity ORDER BY Most_Recent_Foo__c