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?