+ Start a Discussion
charleshowardcharleshoward 

Parent-to-child relationship query for Cases???

I'm trying to pull the set of records that are child cases of another case (they reference the parent case through the "Parent" standard field) in one query.  I can't seem to find an __r field name for this though.  I'd like to be able to do this:

 

[select Id, (select Id, Report_Status__c from Case.Parent__r) from Case where Id in :referencedWorkOrders]

 

But Parent__r is not recognized, and I'm unable to find anything about what the field name might be in our Enterprise WSDL, or by using the Apex Explorer.

 

Is this possible?  Is it not supported because the lookup from Child Cases to Parent Cases is between records of the same object?  Anyone have any other ideas for how to do this (in one query)?

 

Thanks!

Chris JohnChris John

You can search for all cases that have a specific Parent Case but you should use the ParentId field, not the relationship Parent__r

sforce2009sforce2009

If I understand you correctly this is what you are tryig to acheive. You are doing it opposite to your requirement.

 

[select Id, Report_Status__c, Parent.Id, Parent.Report_Status__c from Case where ParentId in: referencedWorkOrders]

 

charleshowardcharleshoward

Thanks for the replies, that's not exactly what I'm trying to do.  The problem is that I'm starting off from a child record of a work order case.  So I can't do ParentId in :referencedWorkOrders because that would pull all of the work order cases that are referenced as parents of the set of Work Order cases.

 

If I were to do this using two queries I would do this

 

List<Id> parentCaseIds = new List<Id>();

for(Case wo : [select Id, ParentId from Case where Id in :referencedWorkOrders])

{

    parentCaseIds.add(wo.ParentId);

}

 

[select Id, Report_Status__c from Case where ParentId in :parentCaseIds and RecordTypeId = complaintRecordType]

 

But I'm trying to compress things into a single query, since we already have an extremely complex case management process, and have already done a bunch of work to bulkify things in order to get under governor limits.

David81David81

Something like this should work

 

 

List<Case> myCases = new List<Case>([select Id, (select Id, Report_Status__c from Cases) from Case where Id in :referencedWorkOrders]);

 

You can then loop through the Cases returned and access an associated list of child Cases

 

 

for(Case c : myCases){
     //do something with your parent cases if need be
     for(Case c2 : c.cases){
     //do something with the children of each parent 
     }
}

 

 

 

charleshowardcharleshoward

Well, that would accomplish what I'm trying to do, but I ran that query against a single case record id in the Apex Explorer, and it comes back with an INVALID_TYPE error, and "Didn't understand relationship 'Cases' in FROM part of query call.

 

That's basically my question, how do you specify the relationship between parent and child cases?  You can do it with just about everything else in SalesForce through something like 'Assets' or 'Site_Activity__r', but apparently not cases...

David81David81

That's odd. This exact code works perfectly for me...

 

 

List<Case> myCases = new List<Case>([select Id, (select Id from Cases) from Case LIMIT 5]);
for(Case c : myCases){
    system.debug('Parent'+c);
    for(Case c2 : c.cases){
         system.debug('Child'+c2);
    }
}

 

Edit: I think I know what's going on. By default, Apex Explorer is using an older version of the API. Open up options and change the endpoint to something like "https://www.salesforce.com/services/Soap/u/16.0". You can also use the Schema Explorer in Eclipse to build your queries

charleshowardcharleshoward

You know, I probably should have thought of that :)  The version setting was the problem in Apex Explorer.  Thanks a lot for the help!