[SalesForce] Soql inner join (Apex)

Morning guys, I am having a issue, I need to make a inner join in soql…
Example:

Process instance( table1)
Process instance step(table 2)

I need to create a table mixing both tables, Id tabel 1 is process instance id in the 2, Table 1 primary key is a forien Key in table 2…

But soql will not support joins for tables
I have to write a complete code for this… do you know any other way apart form Apex class?

Best Answer

So Lets Assume this is your Process Table 1

Id(Primary Key),Name,Description__c,Other_Fields__c

And Process Table 2 Schema Looks like this

Id(Primary Key),Name,Process_Table1__c(Lookup / foreign Key to Table 1), Other_Fields__c

Here you can use Join, to get Parent_table1__c Fields querrying the Process_Table2__c, Its called as child-to-parent traversal

SELECT Id,Name,Process_Table1__r.Name,Process_Table1__r.Description FROM Process_Table2__c

Also, Another kind of join is Parent To child, Inner Querry, which allows yoqueryuerry child for a particular parrent

SELECT Id,Name,Description__c,(SELECT Id,Name FROM Process_Table2__r) FROM Parent_table1__c

Src: https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_relationships_understanding.htm

Related Solutions

[SalesForce] SOQL Query Limit and Data Loading

As @eyescream and @Sdry stated you should do some preprocessing to build up a Set/List of Ids to supply to your SOQL query. You can create maps that can then be used after bulk queries are done. These sort of bulk patterns of building up a collection of Ids for a SOQL query is extremely common in Apex.

This shows using the LoanInfo and assumes that the EquipmentID will be unique in the batch.

Map<Id, LoanInfo> equipmentToLoanInfo = new Map<Id, LoanInfo>();
Set<Id> loanedToIds = new Set<Id>();
for(LoanInfo LI : LoanInfos)
{      
    // Map Equipment ID to Loan Info.
    // Will use keySet() in query later
    equipmentToLoanInfo.put(LI.EquipmentID, LI);

    // store off all of the Ids for later queries
    loanedToIds.add(LI.LoanedTo);
}

Map<Id, User> dbUsers = new Map<Id, User>([
    Select <a bunch of fields> 
    From User 
    Where Id In :loanedToIds
]);
// create Same type of maps for other LoanedTo types...

for(Equipment_Loan__c objELOld : [
    select <a bunch of fields> 
    from Equipment_Loan__c 
    where Equipment__c In :LI.equipmentToLoanInfo.keySet() and Actual_Return_Date__c=null
])
{  
     LoanInfo equipLoanInfo = equipmentToLoanInfo.get(objELOld.Equipment__c);
     if (equipLoanInfo.LoanType == 'User') {
         dbUser = dbUsers.get(equipLoanInfo.LoanedTo);
         // set the fields...

     } else if (equipLoanInfo.LoanType == 'Contact') {
         // same as user, but with contact
     } // continue with other types...
}

If all that you are doing is using the SF Data Loader on the Equipment_Loan__c and all that you need to do is hookup values on a trigger (e.g., before insert, etc.) then you can change the above to use a Map of Equipment_Loan__c records instead of LoanInfo, e.g.,:

Map<Id, Equipment_Loan__c> equipmentToLoanInfo = Trigger.NewMap;
Set<Id> loanedToIds = new Set<Id>();
for(Id eId : equipmentToLoanInfo.keySet())
{      
    Equipment_Loan__c equipLoan = equipmentToLoanInfo.get(eId);

    // store off all of the Ids for later queries
    loanedToIds.add(equipLoan.LoanedTo);
}

// Basically the same code as when using Map<Id, LoanInfo>, 
// but change to use Equipment_Loan__c

The other type of situation that you can run into is where you may need to map a one to many. For example, if your LoanInfos within the same batch could share an EquipmentID, you might want to use processing such as the following.

Map<Id, List<LoanInfo>> equipmentToLoanInfos = new Map<Id, List<LoanInfo>>();
for (LoanInfo LI : LoanInfos) {
    if (equipmentToLoanInfos.get(LI.EquipmentID) == null) {
       equipmentToLoanInfos.put(LI.EquipmentID, new List<LoanInfo>());
    }
    equipmentToLoanInfos.get(LI.EquipmentID).add(LI);
}

[SalesForce] soql inner join

Since the object that these two tables have in common is the User object, you should build your query from that level, and include subqueries for the two child objects (Opportunity and myTerr__c). This is further complicated by the fact that the Opportunity.OwnerId is a standard field that doesn't let you automatically do this. So you need two things:

A custom lookup field on Opportunity with an Apex trigger that updates the value from the OwnerId field. This new field will have a relationship you can query from the User level. Let's say the field is Opportunity_Owner__c and the relationship name is Owned_Opportunities__r.

Example:

trigger opptrigger (before insert, before update) {
    for (Opportunity o : Trigger.new)
        o.Opportunity_Owner__c = o.OwnerId;
}

A query from the User level that includes both child objects together.

Example:

list<User> theseUsers = [select Id,
    (select terr__c from myTerrs__r),
    (select Id from Owned_Opportunities__r)
    from User
    where Id in :someIds];

Now you can loop through the returned Users, and refer to each of the child lists separately.

for (User u : theseUsers) {
    list<myTerr__c> terrs = u.myTerrs__r;
    list<Opportunity> opps = u.Owned_Opportunities__r;
}

I notice that this doesn't exactly give you what you say you want, which is all of the Opportunities in a specific Territory. The above code will only do this if each User has exactly 1 territory. Any more, and there doesn't seem to be a way to identify an Opp with a specific territory. You may want to consider a custom lookup on the Opportunity to the myTerr object, which would make this query very simple.

Best Answer

Related Solutions

[SalesForce] SOQL Query Limit and Data Loading

[SalesForce] soql inner join

Related Topic