15

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'

Sample Object Relationship

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

9

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.

  • Thanks CoryCowgill and LaceySnr. I was able to get all the data in one query as suggested while applying all requested junction filters during this one call. Then I used a map to validate the records returned and removed from the map any main object records that didn't return at least one record from each one of the requested junction objects. After processing, the map only had the main object records that met all requested filter criteria, exactly like the corresponding SQL query. The performance was also a lot better than my first approach of making multiple calls. Thanks for your help. – SamMoreira Apr 30 '15 at 13:17
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.