+ Start a Discussion
Alexandru IleanaAlexandru Ileana 

How can I find out what sObjects can be used in SOQL Parent-to-child joins and why I cannot use all?

Dear all,

I have been using separate queries to obtain related Ticket information for a long time.
Here is an example:

Map<Id, Case> tickets = new Map<Id, Case>([SELECT Id, CaseNumber, Subject FROM Case LIMIT 10]); // just an example
List<CaseTeamMember> ticketTeamMembers = [SELECT Id, MemberId, ParentId FROM CaseTeamMember WHERE ParentId IN :tickets.keySet()];
List<CaseContactRole> customerContactsMembers = [SELECT Id, CasesId, ContactId FROM CaseContactRole WHERE CasesId IN :tickets.keySet()];
... // and so on

What I am trying to do now is to limit the number of queries used so I have managed to come this far:
List<Case> relatedTicketInformation = [SELECT
                                                          (SELECT CreatedById FROM CaseComments),
                                                          (SELECT CreatedById FROM Custom_Ticket_Comments__r),
                                                          (SELECT CreatedById FROM Attachments),
                                                          (SELECT CreatedById FROM Custom_Ticket_Attachments__r)
                                                         FROM Case
                                                         WHERE Id IN :tickets.keySet()];

This query works and returns lists of creatorIds for all 4 objects (the custom ones are made by me).
This are called (If I understand correctly) "Parent-to-Child Relationships".

Usefull links with further information are the following:
http://blog.jeffdouglas.com/2010/02/22/soql-how-i-query-with-thee-let-me-count-the-ways/
http://salesforce.stackexchange.com/questions/20572/soql-join-between-two-standard-objects/20573#20573
https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_relationships_understanding.htm


What I want is the following:
List<Case> relatedTicketInformation = [SELECT
                                                         (SELECT MemberId FROM CaseTeamMembers)
                                                         (SELECT ContactId FROM CaseContactRole)
                                                         FROM Case];

This does not work! I want to know why? How can I find out which objects work and which ones do not?
This is how the object looks in Workbench:
User-added image
I see no information related to the plural form for this object (that is what I need).

To summarise, I would like to know the following:
  1. Do join SOQL queries done in this manner count as 1 towards the 101 query limit or still as 3?
  2. Why some objects work and some do not? Shouldn't it be consistent just like then doing field__r.field on any lookup field (I know this is totally different but the relationship exists anyway and it is visible)?
  3. How do I find out what objects work for this and what objects do not (standard)? And why was the decision to lock this feature for some objects been taken if that is the case? Otherwise, please give me a list of ALL the plural forms of all standard objects which can be used in a join query.
  4. Is such a query more efficient then doing separate queries?
Any answer to this would be apreciated.

Best Regards,
Alexandru.