function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Brendan MBrendan M 

Most efficient way to compare two collections, lists, maps or other

Hi Community, 
 

I'm new to developing and I'm keen to learn the best way to compare two collections for future use on a trigger or method. 

For example for an investigation into org missing data if I wanted to compare Leads and Accounts to check which Leads had matching email addresses with Accounts but other personal information was diffrerent. 

At the moment I only know how to do this with two for loops but there must be a better way? possibly using maps or a map and list?

Account[] personalAccounts = [SELECT Id, FirstName, LastName, Phone, PersonMobilePhone, PersonEmail, EmployerCode__c, DOB__c FROM Account WHERE LastName != null AND PersonEmail != null AND DOB__c != null];

Lead[] leads = [SELECT Id, FirstName, LastName, Phone, MobilePhone, Email, Employer_Code__c, DOB__c FROM Lead WHERE Email != null AND DOB__c != null];

Sudo code

for Leads {
  for Accounts {
   If (Lead.email == Account.PersonalEmail && Lead.DOB__c != Account.DOB__c && Lead.EmployerCode__c != Account.EmployerCode__c etc )

  cleanUpMap.put(Lead.Id, Account.Id);

}
}

Appreciate the help, sorry for the terrible code. 

Best Answer chosen by Brendan M
TobyKutlerTobyKutler
Account[] personalAccounts = [SELECT Id, FirstName, LastName, Phone, PersonMobilePhone, PersonalEmail, EmployerCode__c, DOB__c FROM Account WHERE LastName != null AND PersonEmail != null AND DOB__c != null];
		Lead[] leads = [SELECT Id, FirstName, LastName, Phone, MobilePhone, Email, Employer_Code__c, DOB__c FROM Lead WHERE Email != null AND DOB__c != null];
		//create maps setting the key as the matching value you are checking i.e email, DOB, Employer Code
		Map<String, Account> emailToAccount = new Map<String, Account>();
		Map<Date, Account> dobToAccount = new Map<Date, Account>();
		Map<String, Account> employerCodeToAccount = new Map<String, Account>();
		//loop through accounts to make map each account to the value we are looking to match with lead
		for(Account a: personalAccounts){
			emailToAccount.put(a.PersonalEmail, a);
			dobToAccount.put(a.DOB__c, a);
			employerCodeToAccount.put(a.EmployerCode__c,a);
		}
		//set the result map for successful matches 
		Map<Account, Lead> AccountToLeadWithMatchingDetails = new Map<Account, Lead>();
		for(Lead ld : leads){
				//check to make sure that there is a matching an account for each three fields and that each match is matching to the same account
				if(emailToAccount.get(ld.Email) != null &&  dobToAccount.get(ld.DOB__c) != null && employerCodeToAccount.get(ld.Employer_Code__c) && employerCodeToAccount.get(ld.Employer_Code__c) == dobToAccount.get(ld.DOB__c) == emailToAccount.get(ld.Email) != null){
					AccountToLeadWithMatchingDetails.put(emailToAccount.get(ld.Email), ld);
				}
		}
		return AccountToLeadWithMatchingDetails;
basic idea is to create the value you are looking to match between the two objects as a key in the map. Then you loop through the second object and check to see if it exists on the first object and that all the matches are to the same record
 

All Answers

Tuyen Hoang 10Tuyen Hoang 10
Try this query with Apex: 
select id, AccountId from contact where Email = Account.Email and DOB__c != Account.DOB__c Lead.EmployerCode__c != Account.EmployerCode__c etc

 
TobyKutlerTobyKutler
Account[] personalAccounts = [SELECT Id, FirstName, LastName, Phone, PersonMobilePhone, PersonalEmail, EmployerCode__c, DOB__c FROM Account WHERE LastName != null AND PersonEmail != null AND DOB__c != null];
		Lead[] leads = [SELECT Id, FirstName, LastName, Phone, MobilePhone, Email, Employer_Code__c, DOB__c FROM Lead WHERE Email != null AND DOB__c != null];
		//create maps setting the key as the matching value you are checking i.e email, DOB, Employer Code
		Map<String, Account> emailToAccount = new Map<String, Account>();
		Map<Date, Account> dobToAccount = new Map<Date, Account>();
		Map<String, Account> employerCodeToAccount = new Map<String, Account>();
		//loop through accounts to make map each account to the value we are looking to match with lead
		for(Account a: personalAccounts){
			emailToAccount.put(a.PersonalEmail, a);
			dobToAccount.put(a.DOB__c, a);
			employerCodeToAccount.put(a.EmployerCode__c,a);
		}
		//set the result map for successful matches 
		Map<Account, Lead> AccountToLeadWithMatchingDetails = new Map<Account, Lead>();
		for(Lead ld : leads){
				//check to make sure that there is a matching an account for each three fields and that each match is matching to the same account
				if(emailToAccount.get(ld.Email) != null &&  dobToAccount.get(ld.DOB__c) != null && employerCodeToAccount.get(ld.Employer_Code__c) && employerCodeToAccount.get(ld.Employer_Code__c) == dobToAccount.get(ld.DOB__c) == emailToAccount.get(ld.Email) != null){
					AccountToLeadWithMatchingDetails.put(emailToAccount.get(ld.Email), ld);
				}
		}
		return AccountToLeadWithMatchingDetails;
basic idea is to create the value you are looking to match between the two objects as a key in the map. Then you loop through the second object and check to see if it exists on the first object and that all the matches are to the same record
 
This was selected as the best answer
TobyKutlerTobyKutler
The other way to do it depending on your requirement is to query the second object based on your first. Would look something like this: 
  
Account[] personalAccounts = [SELECT Id, FirstName, LastName, Phone, PersonMobilePhone, PersonalEmail, EmployerCode__c, DOB__c FROM Account WHERE LastName != null AND PersonEmail != null AND DOB__c != null];
		List<String> accountEmails = new List<String>();
		List<Date> accountDobs = new List<Date>();
		List <String> employCoded = new List <String>();
		for(Account a: personalAccounts){
			accountEmails.add(a.PersonalEmail);
			accountDobs.add(a.DOB__c);
			employCodes.add(a.Employer_Code__c);

		}
		Lead[] leads = [SELECT Id, FirstName, LastName, Phone, MobilePhone, Email, Employer_Code__c, DOB__c FROM Lead WHERE Email IN :accountEmails AND DOB__c IN : accountDobs AND Employer_Code__c IN :employCodes];