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!

Comments (0)

Thanks! Your comment is awaiting moderation.

Testimonials

They have a very good company culture of their own, which gives them a real edge compared to other providers.

CEO

Leading UK system integrator

They're very skilled technically and are also able to see the bigger picture.

Managing Partner

Scalable SaaS for healthcare IoT built on Salesforce platform

They've been consistently able to deliver work on time and within budget.

CTO

High performance SaaS for financial insitutions

We are seriously impressed by the quality and broader picture of anything they do for us.

CEO

Gold trading platform

Speak with an Expert

How can we help? If you would like a member of the Stratoflow team to get in touch, please send us your message and we will contact you shortly.

Contact