+ Start a Discussion
JesseAJesseA 

Querying for related-related records

I've already written my apex out but just want to find out if there is a more efficient way to do what I am doing.

 

I have three objects: Device, Application, Contract

There are two join objects to link them and provide a many to many relationship: Device-Applications and Contract-Applications

 

There is a date field on Device. For every Contract I would like to show the earliest Device Date out of all the related Devices. So from Contracts that would mean Finding all Contract-Apps then using that to find all Device-Apps and using that to find the related Devices so that i can find the earliest date. Currently I am doing this with 3 soql queries:

1. [SELECT Id... FROM Contract-App WHERE Contract__c IN : contractIdsToUpdate]

2. [SELECT Id... FROM Device-App WHERE Application__c IN : appIdsFound]

3. [SELECT Id... FROM Device__c WHERE Id IN : deviceIds]

 

Is there a more efficient way to write these queries or even the whole process?

 

 

Best Answer chosen by Admin (Salesforce Developers) 
gm_sfdc_powerdegm_sfdc_powerde

You should be able to do it with one query.   (1) can become a sub-query for (2).  In the select clause of (2), use Device relationship to get the fields from device directly.   Something like this.

 

 

SELECT Device__r.Id, Device__r.xyz,... FROM Device-App WHERE Application__c IN (SELECT Id... FROM Contract-App WHERE Contract__c IN : contractIdsToUpdate)

 

 

 

All Answers

gm_sfdc_powerdegm_sfdc_powerde

You should be able to do it with one query.   (1) can become a sub-query for (2).  In the select clause of (2), use Device relationship to get the fields from device directly.   Something like this.

 

 

SELECT Device__r.Id, Device__r.xyz,... FROM Device-App WHERE Application__c IN (SELECT Id... FROM Contract-App WHERE Contract__c IN : contractIdsToUpdate)

 

 

 

This was selected as the best answer
JesseAJesseA

Thanks for the reply gm but didn't get your example as it was the same as mine. I think I know what your talking about with sub-queries just not sure how to structure them. Can you elaborate?

 

EDIT: Nevermind, your post was updated after I made this post. Thanks I will try it out right now.