SOQL querying multiple levels of master-detail related objects - Answers - Salesforce Trailblazer Community
Trailblazer Community
Ask Search:
Kelly LoganKelly Logan 

SOQL querying multiple levels of master-detail related objects

We need to pull data from multiple objects that are related at different levels through master detail relationships for a sync with an external system. The top is the Contact object. In each case we want to pull data when anything in any of the related objects has been updated since the last sync (which we keep track of in a separate log object). 

Here is the list of relationships (Master -< Detail):
Contact -< Application -< Student Term -< Student Term Payment
Contact -< Application -< Appeal
Contact -< FAFSA Detail

My solution right now is to query each separately and present them in a set of lists that can be stitched together with their foreign keys. No processing needs to be done as a single object so this seems the most efficient (only pull the data that was modified, leave other related objects' data alone if it hasn't been changed) and the easiest. 

I am curious though - could this be done as a single query? I don't see how it could be, even if you started with Student Term as base because as far as I understand you can't go up one level Detail to Master then back down from the Master to a different Detail object. If there is a way to do this though I'd love to hear.

Alternately, is there a way to create something like an SQL View form that would encapsulate all these queries into a single queryable object? Like creating a multi-object report and then running it remotely through Apex and gathering the results for use?
Best Answer chosen by Kelly Logan
Kelly LoganKelly Logan
Here's the question in the Developer forum. Please direct any followups here: https://developer.salesforce.com/forums#!/feedtype=SINGLE_QUESTION_DETAIL&dc=Developer_Forums&criteria=ALLQUESTIONS&id=9060G000000MRWDQA4

All Answers

Amit SinghAmit Singh
Kelly,

I believe it can be done via Parent Child OR child to Parent SOQL and nested SOQL queries See below links

https://developer.salesforce.com/page/A_Deeper_look_at_SOQL_and_Relationship_Queries_on_Force.com
http://www.sfdc99.com/2013/06/24/example-how-to-write-a-cross-object-soql-query-part-2/
http://www.sfdc99.com/2013/06/09/example-how-to-write-a-cross-object-soql-query/

As your query is related to Development then I would suggest you to post this question to developer board for the quick response. This community is focused on configuration and design questions. Programming questions are best to post in one of the developer forums for a quick response here
https://developer.salesforce.com/forums/#!/feedtype=RECENT&criteria=ALLQUESTIONS
http://salesforce.stackexchange.com/


Regards,
Amit Singh
GIRIKON LLC
https://sfdcpanther.wordpress.com/
Kelly LoganKelly Logan
Posting in the Developer community is a good idea, thanks Amit!

I'll follow up there and close this down. If anyone is interested in answers on this, feel free to contact me later.
Kelly LoganKelly Logan
Here's the question in the Developer forum. Please direct any followups here: https://developer.salesforce.com/forums#!/feedtype=SINGLE_QUESTION_DETAIL&dc=Developer_Forums&criteria=ALLQUESTIONS&id=9060G000000MRWDQA4
This was selected as the best answer