[SalesForce] Complicated Query with many subqueries

So I have a rather complicate query that I am trying to do all in one step which may or not be possible

To try and explain I have custom objects called "paths" and paths are specialized content available for specific types of users tied to specific "actions" which are our main content. Theres two other custom objects. Path Groups (which is just a master detail with paths) and then (Path Group Permissions) which is what grants permission to accounts to specific paths..

An example might help

So we have a path group called Homeownership, this path contains many little bits of info (called paths)… and then certain portal users can see this information so they have a path group permission on their account record

So the question is, my visualforce page is looping through all the actions and I want it to also display any paths available to the user.

This bit of code is working now:

string userId = UserInfo.getUserId();
User u = [select ContactId, Email, FirstName, LastName, Title 
          from User where Id=:userId];

Contact c = [select id, AccountId, Contact.Account.Name, Contact.Account.Id
             from Contact where id = :u.ContactId];

List<Path_Group_Permission__c> pgp = [select Path_Group_Permission__c.Path_Group__r.id 
    from Path_Group_Permission__c
    where Path_Group_Permission__c.Account__r.Id = :c.Account.Id];

Actions =  [Select Name, body__c, Action_Type__c, Caution__c,  URL_Title__c, 
    (select Path__c.Id, Path__c.Name, Path__c.Body__c from Action__c.Paths__r
        where Path__c.Path_Group__c = :pgp[0].Path_Group__r.id)
    from Action__c order by Order__c ];

the problem is its only checking the first "PGP" or path group permission. I feel like I should be able to use "IN" to check all of them but I am not having any luck

I tried

Path__c.Path_Group__c IN :pgp
Path__c.Path_Group__c IN :pgp.Path_Group__r.id
and adding the pgp query into the actions query like Path__c.Path_Group__c in **subquery**

no luck… any ideas?

Best Answer

Iterate over the pgp list and accumulate all the path group ids in a List and then use this as the predicate for an IN condition

List<Id> pgIds = new List<Id>{};
For (Path_Group_Permission__c pgperm : pgp)
pgIds.add(pgperm.Path_Group__r.id);

And then use the collection in the Where

where Path_c.Path_Group__c IN :pgIds

As an aside you're not filtering on Actions, so could there be Action records which don't have associated Path records?

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] How many SOQL queries are spent for subqueries

Let's take the following SOQL:

List<Account> dd = [
    SELECT 
        id, 
        (SELECT Name FROM Contacts),  // <- first subquery
        (SELECT AccountID FROM Cases) // <- second subquery
    FROM 
        Account
    WHERE 
        id 
    IN (SELECT AccountID FROM Case)   // <- not counted
];

In the above query there are total 3 subqueries + 1 parent query. So, there are 4 queries. But, Salesforce doesn't count subqueries against governor limit of 100 SOQLs. It counts only root query which means only 1 SOQL query would be consumed.

Subqueries are counted separately as AggregateQueries. You can check those with Limits.getAggregateQueries() and Limits.getLimitAggregateQueries(). You cannot have more than 300 aggregations in a single transaction. This is stated just below the sentence you quoted:

In a SOQL query with parent-child relationship subqueries, each parent-child relationship counts as an extra query. These types of queries have a limit of three times the number for top-level queries

So your count would be:

N = Number of SOQL (root query)
A = Number of Aggregation

Safe Query Limit = N <= 100 && A <= 300

Best Answer

Related Solutions

[SalesForce] SOQL Query Limit and Data Loading

[SalesForce] How many SOQL queries are spent for subqueries

Related Topic