Retrieve earliest record from each Grand child object

Question

I have 3 objects, Object A, Object B and Object C. Object B is a child of Object A, while Object C is a child of object B. I am trying to access the all the single earliest records of all records in Object C that are related to object A through object B through SOQL code. Any clues? Here are some of the SOQL queries I've come up with but they only get the earliest single record from Object C related to Object B

Object A -> Object B -> Object C

 EarliestRecordsObjectCOnly = [SELECT Name, ObjectB__c, Meeting_Date__c FROM ObjectC__c WHERE ObjectB__c =:id ORDER BY Meeting_Date__c ASC LIMIT 1];
//these SOQL queries get the earliest single record from Object C related to Object B

Tried the SOQL code below and I get the error that Didn't understand relationship 'ObjectC__c' in field path

EarliestRecordsObjectCOnly  = [SELECT Id, (SELECT ObjectB__c,  Meeting_Date__c FROM ObjectC__c WHERE ObjectB__c.ObjectA__c =:id ORDER BY Meeting_Date__c ASC LIMIT 1) FROM ObjectA__c];

Any ideas? Let me know if there I need to be clearer. Thanks in advance

Answer 1

I don't have the right data structure setup anywhere to test this, but this should work:

 ObjectB__c[] list = [SELECT Name,
                         (SELECT Name, Meeting_Date__c 
                          FROM ObjectC__r 
                          ORDER BY Meeting_Date__c ASC LIMIT 1 )
                     FROM ObjectB__c 
                     WHERE ObjectB__c =:objectAId 
                     ORDER BY ObjectBDateField__c ASC LIMIT 1];

Note I have a list as the top level item - it's much safer to query with a list, even if you have a LIMIT 1 clause. From here, you should be able to access your items like so:

ObjectC__c = list[0].ObjectC__r

You may want to remove the LIMIT 1 from the subquery also and just get a list, where you can extract the 0th indexed property.

Hope this helps!

Oh, it's possible there's a better way to do this - but I do know this should work.

Note Parent-Child relationships use the PLURAL form of the object name, ie in the below example, Daughter__c becomes Daughters__r

//Child to parent
SELECT Id, FirstName__c, Mother_of_Child__r.FirstName__c
FROM Daughter__c
WHERE Mother_of_Child__r.LastName__c LIKE 'C%'

//Parent to child
SELECT LastName__c,
  (
   SELECT LastName__c
   FROM Daughters__r
  )
FROM Mother__c