1

I'm looking to run a query from a custom object (called RS) related to Case (Master Detail) where I need to get a list of the contacts related to the Account on the Case.

I can run the following query from the case and get the information, but I need to the get the same information running it from the RS child object:

SELECT Name, Id
FROM Contact
WHERE AccountId IN (SELECT AccountId FROM Case where id = '500XXXX')

I think it would look something like this:

select name, (select Name, id from Contact) from account where id IN (select Case.AccountId from RS__c where id = 'a1234XXX')

but get the error "inner select field cannot have more than one level of relationships"

Is it even possible to get this info with one query?

0

2 Answers 2

3

You say that your RS object is in a Master-Detail relationship with Case. Assuming that RS is the detail side of the relationship, you simply need to loop over your RS records and gather the related Case Ids.

With that, you should be able to run your query with one small change.

// Declare either a set or a list to hold the Ids.
// It doesn't really matter which one, my preference is for a Set though.
Set<Id> caseIdsSet = new Set<Id>();
for(RS__c rsRec :myRsRecordList){
    // I'm assuming the relationship field is Case__c
    // Even though the relationship field will show you the name of the related
    //   record in Visualforce (guessing the same is true for Lightning), the field, in
    //   reality, holds the Id of the related record.
    caseIdsSet.add(rsRec.Case__c);
}

List<Contact> contactsList = [SELECT Name, Id
                              FROM Contact
                              WHERE AccountId IN (SELECT AccountId
                                                  FROM Case
                                                  // This is the small change to your query
                                                  WHERE Id IN :caseIdsSet
                                                 )];

If this code is part of a trigger on your RS object, then this is the only query that you'll need. If you're not doing this as part of a trigger (or otherwise don't have access to the Trigger context variables), then you will need another query to grab the required field(s) from your object.

+edit:

So you're outside of a trigger context. In that case, there's only one way I can think of the avoid the second query; use a formula field.

Update to edit:

formula fields can't be used in semijoins. There is still merit in the example below, but to use it in your situation, you'd still likely need to query RS__c records to obtain the value of the formula field (and then you'd be using code based on my first example).

If you have a formula field that pulls down the AccountId from the Case your RS record is related to, you could use that in your semi-join like so...

List<Contact> contactsList = [SELECT Name, Id
                              FROM Contact
                              WHERE AccountId IN
                                  (SELECT Account_Id_Formula__c 
                                   FROM RS__c
                                   WHERE Id IN :myRSRecordsList
                                  )
                          ];

While the most common use of a semijoin is where you'd query the object whose Ids you're trying to obtain (i.e. Account in this case), it'll work on any object provided that you have a field that holds the appropriate Id on that object (I don't believe you're allowed any hierarchy traversal, up or down, in a semijoin).

6
  • Thank you very much for the detailed answer, but this wouldn't be running from a trigger. Is it a limitation in SOQL where this information can't be pulled from one query?
    – Carlos F
    Oct 16, 2017 at 19:56
  • @CarlosF Pretty much, yeah. You can only have one level of sub-queries (so WHERE Id IN (SELECT Id FROM Case WHERE Id IN (SELECT Case__c FROM RS__c ...)) won't work). You can traverse up to 5 levels upwards in a hierarchy, but only one level down a hierarchy. Thus, to get information about Contacts, you either need to query the Contact object itself, or SELECT Id, (SELECT ... FROM Contacts) FROM Account .... RS is two levels down the hierarchy from Account, and not directly related to Contact, so 2 queries are required.
    – Derek F
    Oct 16, 2017 at 22:11
  • @CarlosF About the only way I can think of possibly avoiding a second query, even outside of a trigger context, would be to create a formula field. I'll edit my answer to elaborate.
    – Derek F
    Oct 16, 2017 at 22:16
  • Thank you for the explanation and examples! I tried using the formula field and get the error "semi join sub selects can only query id fields, cannot use IDformula__c". Perhaps I can try to create a relationship between the account and the custom object, so that it will only be one level relationship.
    – Carlos F
    Oct 17, 2017 at 14:05
  • @CarlosF I suppose that's what I get for suggesting something without trying it myself first. Having a direct relationship between Account and RS__c would allow you to only use a single query (though you'll need something like a trigger to populate that lookup, so I'm not sure that's much better).
    – Derek F
    Oct 17, 2017 at 14:44
0

Instead of querying the Case object, query the related object instead, and use a filter on the relationship field.

SELECT Id, Name, Account.Name 
FROM Contact
WHERE AccountId IN (
    SELECT AccountId 
    FROM RS__c
    WHERE Case__c = '...'
)

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .