Retrieving data of an object from another one – tips and tricks
You shouldn’t use SOQL, SOSL and DML operations in loops. It’s very crucial facet because by doing so you can very easily cross the limits of transaction and make your functionality to work only with limited number of objects which could cause all sorts of problems.
By doing so, in the worst case scenario, with SOSL and SOQL, you need to make the search process that depending on fields from other objects.
Let me show you now a bad example of using SOQL in for loop and ending up with SOQL exception thrown if you have more than 100 accounts:
Id ownerId = // required id
List <Account> accounts = [Select Id From Account Where User.Id =: ownerId ];
for (Account account : accounts){
Contact contact = [Select Id From Contact Where Contact.Account.ID =: account.ID;
….
}As you can see, we’ve got a slight problem. In order to make this code functional, without throwing an exception, you need to use loop and add to the list these fields that you want to use in the next SOQL. By doing so we will have every condition field in one list. You alco cannot forget about including field, in the first SOQL, connects both objects (In the following example it is a ContactId).
Id ownerId = // required id
List <Account> accounts = [Select Id, ContactId From Account Where User.Id =: ownerId ];
List <Id> accountsIDs = new List <Id> ();
for (Account account : accounts){
accountsIDs.add(account.Id);
}In this situation we can add new SOQL search operation for Contacts:
Id ownerId = // required id
List <Account> accounts = [Select Id, ContactId From Account Where User.Id =: ownerId ];
List <Id> accountsIDs = new List <Id> ();
for (Account account : accounts){
accountsIDs.add(account.Id);
}
List<Contact> contacts =[Select Id From Contact Where Contact.Account.ID IN: accountsIDs];This new list of contacts contains every single Contact that would be retrieved in the loop. At this point we need to create new Map. If our objects are triangle by Id then creating Map is fairly simple, because map in Apex get method putAll(List<sObject>) which allow us to put List into Map and key values are automatically assigned and are equal to Id values of the objects in the List.
Id ownerId = // required id
List <Account> accounts = [Select Id, ContactId From Account Where User.Id =: ownerId ];
List <Id> accountsIDs = new List <Id> ();
for (Account account : accounts){
accountsIDs.add(account.Id);
}
List<Contact> contacts =[Select Id From Contact Where Contact.Account.ID IN: accountsIDs];
Map<Id, Contact> contactsByIds = new Map<Id, sObject>();
contactsByIds.putAll(contacts);If you don’t want to triangle objects by Ids then you need to create loop to put all objects with custom keys in them. In the first SOQL you must retrieve proper field that will connect both objects.
Id ownerId = // required id
List <Account> accounts = [Select Id, ContacName From Account Where User.Id =: ownerId ];
List <Id> accountsIDs = new List <Id> ();
for (Account account : accounts){
accountsIDs.add(account.Id);
}
List<Contact> contacts =[Select Id, Name From Contact Where Contact.Account.ID IN: accountsIDs];
Map<String, Contact> contactsByNames = new Map<Id, sObject>();When everything is ready we can create loop where we will implement required functionality. Contacts will be retrieved from Map by field from iterated object which connect both objects. In the example above it will be a Contact Name and in the example below it is a ContactId.
Id ownerId = // required id
List <Account> accounts = [Select Id, ContactId From Account Where User.Id =: ownerId ];
List <Id> accountsIDs = new List <Id> ();
for (Account account : accounts){
accountsIDs.add(account.Id);
}
List<Contact> contacts =[Select Id From Contact Where Contact.Account.ID IN: accountsIDs];
Map<Id, Contact> contactsByIds = new Map<Id, sObject>();
contactsByIds.putAll(contacts);
for (Account account : accounts){
Contact contact = contactsByIds.get(account.ContactId ;To to sum all this stuff up, if we write SOQL query in this way, exception won’t be thrown. As simple as that. I hope my examples will clarify some things and help you out in your personal projects. Good luck!
