4

For example I have the following code:

Set<ID> ReferralID = new Set<ID>();
List<Account> Referrals_List = new List<Account>();

// Create a set of Id's of the Referrer records.
for (Account a:newRec) {
    if (a.miiApp__Referrer__c != null) {
        ReferralID.add(a.miiApp__Referrer__c);
    }
}

// Fetch the referral records as per id set
Referrals_List = [SELECT Id
                        , Original_Source__c
                        , Is_Alliance__c
                        , Is_Client_Manager__c
                        , Original_Source_Account__c
                    FROM Account
                    WHERE Id IN :ReferralID];

The code above loops through the list of new records inside a trigger method to create a set of id's that I need to fetch from the database.

Ideally I would like to be able to fetch my "Referrals_List" in one call to the database rather than having to loop over the newrecords.

Something along the lines of:

Referrals_List = [ SELECT * From Account 
                    WHERE miiApp__Referrer__c in:[SELECT Id 
                                                 FROM Account WHERE Id:newRec].Id];

What I forgot to add is that after fetching the Referrals_List I also looped again to build the Map:

Map<ID,Account> Referrals_Map = new Map<ID,Account>
Referrals_List = [select ID,Original_Source__c, Is_Alliance__c,Is_Client_Manager__c,Original_Source_Account__c from Account where ID in:ReferralID];
// Create a map of the referral list
for(Account a : Referrals_List){
     Referrals_Map.put(a.ID, a);
}
1
  • Something along the lines of? Referrals_List = [ SELECT * From Account WHERE miiApp__Referrer__c in:[SELECT Id FROM Account WHERE Id:newRec].Id];
    – Vincent
    Oct 9, 2014 at 0:35

2 Answers 2

1

The simple answer is no, there isn't a better way. SOQL can perform subselects but you can't subselect from the same object as your main select, so you can't do anything like

list<Account> accs = [SELECT Name FROM Account WHERE ParentId IN (SELECT Id FROM Account WHERE Name LIKE 'Ar%')];

If you try this, you will get the error

The inner and outer selects should not be on the same object type

But I get the feeling what you're really asking for is a way to pull the set of Ids out of your newrec list directly in SOQL without reading through them in the Apex. That's not possible either.

1

My solution with one less loop

    Set<ID> ReferralID = new Set<ID>();
    // Create a set of Id's of the Referrer records.
    for(Account a:newRec){
        if(a.miiApp__Referrer__c!=null) ReferralID.add(a.miiApp__Referrer__c);  
    }
    // Fetch the referral records as per id set
    Map<ID,Account> Referrals_Map = new Map<ID,Account>([select ID,Original_Source__c,  Is_Alliance__c,Is_Client_Manager__c,Original_Source_Account__c from Account where ID in:ReferralID]);

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .