SOQL Best Practices with Multiple Junction Objects

Question

I need some help with SOQL. I have a SQL background and I'm trying to understand how a simple query in SQL could be written in SOQL.

Let's supposed I have one main object that is part of multiple junction objects and a query needs to be written to select records from the main object that meet certain criteria related to the other parents of the junction objects. What would be the best approach to avoid reaching Salesforce limits and work around SOQL limitations?

For instance, I need to retrieve all records from Main Object that are associated with Object 1 (via Junction 1) and Object1.Field1 = 'value1', and also associated with Object 2 (via Junction 2) and Object2.Field1 = 'value2', and also associated with Object 3 (via Junction 3) and Object3.Field1='value3'.

In SQL, I would start with something like this (see image below for objects relationship):

Select Distinct [Main ID], [Field 1], [Field 2] From [Main Object] 

Inner Join [Junction 1] On [Junction 1].[Main ID] = [Main Object].[Main ID] 
Inner Join [Object 1] On [Junction 1].[Object1 ID] = [Object 1].[Object 1 ID] And [Object 1].[Field 1]='value1'

Inner Join [Junction 2] On [Junction 2].[Main ID] = [Main Object].[Main ID] 
Inner Join [Object 2] On [Junction 21].[Object2 ID] = [Object 2].[Object 2 ID] And [Object 2].[Field 1]='value2'

Inner Join [Junction 3] On [Junction 3].[Main ID] = [Main Object].[Main ID] 
Inner Join [Object 3] On [Junction 3].[Object31 ID] = [Object 3].[Object 3 ID] And [Object 3].[Field 1]='value3'

In SOQL, I attempted to write a similar query using sub-queries for the junction objects filters, but Salesforce prevented me from having more than two sub-queries in a single call even though they're all related to the main parent object.

In on of my last attempts to get this to work, I made a SOQL call to retrieve records from Main Object and added the results to a map (main). Then, I made separated calls to the junction objects to retrieve Main IDs that match each criteria, and also added the returned records to a map (junction map). Once I had the Main IDs, I had to iterate through my junction map and try to find the returned IDs in my main map. If the IDs were not found in the main map, I would remove the record from the main map (because it failed the requested criteria). The same approach would be applied to junction 2 and junction 3. The records remaining in main map would then be the result of records that match all requested criteria for all junction objects.

Main issue is that I'm not sure if that's the best practice when dealing with multiple junction object as I'm dealing here with multiple calls to the database and lots of processing. In my sample, I only addressed 3 junctions, but in my application, I have more than 3 junctions. I'm concerned, I might not be taking the correct approach here.

Does anyone have any experience on the best approach when dealing with multiple junction objects or could at least direct me to proper documentation that could help me?

Thanks,

SM

Answer 1

Salesforce Object Query Language (SOQL) does not support JOIN operations that SQL does. The reason for this is the multi-tenant nature of the Force.com platform. https://developer.salesforce.com/page/Multi_Tenant_Architecture

SOQL does have a feature called Relationship Queries. https://www.salesforce.com/us/developer/docs/dbcom_soql_sosl/Content/sforce_api_calls_soql_relationships.htm

You can use Parent / Child Queries to save yourself SOQL calls and improve performance, however you cannot go more than 1 level down to Children and more than 5 levels up for Parents.

So you can do for example:

Select Id, Name,
    (Select Id, Name, Object_1__r.Name from Junctions_1__r),
    (Select Id, Name, Object_2__r.Name from Junctions_2__r),
    (Select Id, Name, Object_3__r.Name from Junctions_3__r)
from Main_Object__c

Which is letting you get the data in one query.

You will still need to use MAP's in APEX to JOIN data across the junctions.

I hope that helps you.

Answer 2

Relationship Queries is a nice feature and can be really useful in this case though it has it's limits such as "only one level of parent-to-child relationship can be specified in a query.". So another approach can be to add formula fields to your child objects to retrieve data from their parents then query the child object and you'll have all your data in place.