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?