function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
BroncoBoyBroncoBoy 

Returning a List of Contacts based on info from 2 other related objects

Ultimate goal:  I need to end up with a list of 100 Contacts based on two other related objects and certain criteria.  
Background:  The Account object is related to two other objects Contact (lookup, 1 Account can have many Contacts) and I have another object, a custom object, BD__c, with a master detail relationship to the Account object (1 Account can have many BD__c's).   

To start with, I return a list of these BD__c and I want to order them by Opportunity_Amount__c (descending):

BD__c record #1 Opportunity_Amount__c = $100,000.
BD__c record #2 Opportunity_Amount__c = $50,000.
BD__c record #3 Opportunity_Amount__c = $20,000.
BD__c record #4 Opportunity_Amount__c = $0.

Problem to solve:  Since each BD__c is associated to an Account I want the contacts from the associated account from BD record #1, record #2, record #3 (and so forth) until the total # of contacts reaches 100. In other words I want the first 100 contacts associated with accounts related to the BD__c records who have the highest Opportunity_Amount__c.

So far the code I have is:  

Set<Id> setOfBDIds = new Set<Id>();
for (BD__c bbb : [SELECT b.Account__r.Id  FROM BD__c WHERE Opportunity_Amount__c > 0 ORDER BY Opportunity_Amount__c DESC]) 
{
    setOfBDIds.add(bbb.Account__r.Id);
}
List<Contacts> contacts = [SELECT AccountId, Name FROM Contact WHERE AccountId IN =: setOfBDIds LIMIT 100];

The problem is that, with this structure, the contacts in the list won't necessarily be from the BD__c/Accounts who have the highest Opportunity_Amount__c - they can be from any account.

I thought about trying this using SOQL but according to this:  https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_relationships_query_limits.htm (https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_relationships_query_limits.htm" target="_blank)

"In each specified relationship, only one level of parent-to-child relationship can be specified in a query"

Any thoughts on how this can be acheived? 

Thanks in advance!
Best Answer chosen by BroncoBoy
Thiyagarajan Selvaraj (SFDC Developer)Thiyagarajan Selvaraj (SFDC Developer)
Hi BroncoBoy,

You simply achieve this by modifying your code slightly. Tty below
 
List<Contact> contactsList = new List<Contact>();
Set<Id> setOfBDIds = new Set<Id>();
List<BD__c> bbbList = [SELECT b.Account__r.Id  FROM BD__c WHERE Opportunity_Amount__c > 0 ORDER BY Opportunity_Amount__c DESC];
for (BD__c bbb : bbbList) {
    setOfBDIds.add(bbb.Account__r.Id);
}
Map<Id, Account> accountsMap = new Map<Id, Account>([SELECT Id, (SELECT Id, Name FROM Contacts) FROM Account WHERE AccountId IN: setOfBDIds]);

for (BD__c bbb : bbbList) {
   for (Contact con : accountsMap.get(bbb.Account__r.Id).Contacts){
   		if(contactsList.size() < 100) contactsList.add(con);
   }
}

 

All Answers

Thiyagarajan Selvaraj (SFDC Developer)Thiyagarajan Selvaraj (SFDC Developer)
Hi BroncoBoy,

You simply achieve this by modifying your code slightly. Tty below
 
List<Contact> contactsList = new List<Contact>();
Set<Id> setOfBDIds = new Set<Id>();
List<BD__c> bbbList = [SELECT b.Account__r.Id  FROM BD__c WHERE Opportunity_Amount__c > 0 ORDER BY Opportunity_Amount__c DESC];
for (BD__c bbb : bbbList) {
    setOfBDIds.add(bbb.Account__r.Id);
}
Map<Id, Account> accountsMap = new Map<Id, Account>([SELECT Id, (SELECT Id, Name FROM Contacts) FROM Account WHERE AccountId IN: setOfBDIds]);

for (BD__c bbb : bbbList) {
   for (Contact con : accountsMap.get(bbb.Account__r.Id).Contacts){
   		if(contactsList.size() < 100) contactsList.add(con);
   }
}

 
This was selected as the best answer
BroncoBoyBroncoBoy
Thank you that will work!

(FYI this can build your map size quickly. If each account has 10 contacts limits are quickly met so I had to add quite a few filters and the LIMIT key word)

In case anyone can use it, the final code (with minor corrections):
 
List<Contact> contactsList = new List<Contact>();
Set<Id> setOfBDIds = new Set<Id>();
List<BD__c> bbbList = [SELECT b.Account__r.Id  FROM BD__c b WHERE Opportunity_Amount__c > 0 ORDER BY Opportunity_Amount__c DESC];

for (BD__c bbb : bbbList) 
{
	setOfBDIds.add(bbb.Account__r.Id);
}
system.debug('setOfBDIds size ' + setOfBDIds.size());
Map<Id, Account> accountsMap = new Map<Id, Account>([SELECT Id, (SELECT Id, Name FROM Contacts WHERE Status__c = 'Active' AND Organization__c = 'FFF') FROM Account WHERE Id IN: setOfBDIds AND Organization__c = 'FFF' LIMIT 5]);			

for (BD__c bbb : bbbList) 
{
	if(accountsMap.containsKey(bbb.Account__r.Id))
	{
		for (Contact con : accountsMap.get(bbb.Account__r.Id).Contacts)
		{
            if(contactsList.size() < 100)
            {
                contactsList.add(con);
            }
            else
            {                
            	break;   
            }    
            
		}
	}
}