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
Jon PlyleyJon Plyley 

SOQL Query with complicated custom object connections

Hello everyone, I am writing a utility class for a trigger. I'm trying to execute a SOQL query that pulls all opportunities, then filters them out by start date and by specific types of classifications. So, the objects that I'm using are: opportunities, contacts, and a custom object called classification type contacts. 

I need to filter out the opportunities by specific classification type contacts. 

The relationships are as follows: 
  • opportunities has a lookup field called "donors" that relates it to contacts
  • contacts has a master-detail relationship with "classification_type_contacts__c"
So far, my SOQL query looks as follows:
  
String classNames = getAggregateDataUtils.getClassNames('Majors'); //utility method to return string of names to filter by
Date startDate = Date.newInstance(2018, 11, 27);
Date endDate = Date.newInstance(2018, 11, 28);
List<Opportunity> opps = [SELECT opp.id, opp.CloseDate, opp.amount, (SELECT Classification_Name__c FROM Classification_Type_Contact__c) FROM Opportunity opp
                          WHERE (opp.CloseDate >=: startDate and opp.CloseDate <=: endDate)
                          AND classification_name__c IN : classNames];
The error I get is as follows:

User-added image


I'm not really sure how to fix this. I think the biggest problem is that there's no lookup field on the Contact to the "Classification_Type_Contact__c", but since there's a Master-Detail relationship, I should still be able to get there, right? I can pull reports with a custom report type that relates the three different objects. 

Thank you in advance for your help!


Best,
Jon
 
v varaprasadv varaprasad
Hi Jon,

Here opportunity and Classification is not having any relationships, So that is the reason your query will not work. 
Try like this : 
  
String classNames = getAggregateDataUtils.getClassNames('Majors'); //utility method to return string of names to filter by
Date startDate = Date.newInstance(2018, 11, 27);
Date endDate = Date.newInstance(2018, 11, 28);

List<Contact> cons = [select id,(select id,CloseDate,amount from Opportunities),(SELECT Classification_Name__c FROM Classification_Type_Contact__r) from contact];


for(Contact c : cons){
    //List of classification
    list<Classification_Type_Contact__c> = c.Classification_Type_Contact__r;
	
	//list opportunities
    for(Opportunity op  : c.Opportunities){
	if(op.CloseDate >=: startDate && op.CloseDate <=: endDate && 
	    classNames.contains(op.classification_name__c)){
		   Do your logic here
		
		}
	}
}

Hope this helps you!
If my answer helps resolve your query, please mark it as the 'Best Answer' & upvote it to benefit others.


Thanks
Varaprasad
Salesforce Freelance Consultant/Developer/Administrator
@For Salesforce Project Support: varaprasad4sfdc@gmail.com

Salesforce latest interview questions  :
https://www.youtube.com/channel/UCOcam_Hb4KjeBdYJlJWV_ZA?sub_confirmation=1
 
S_RathS_Rath
Hi Jon, 

When we are doing parent-child query and using a relationship name in a query, you must use the relationship names without the __c. Instead, append  __r (underscore r). Sothe  relationship name would be like Classification_Type_Contact__r. Also, you need to use correct Child Relationship Nameand it should be Classification_Type_Contacts.

You can get Child Relationship Name by following below steps:
- Go to Classification_Type_Contact__c object -> Fields and Relationships -> Contact Field -> You can see Child Relationship Name

You need to correct you query that is line no.5 -->>
List<Contact> cons = [select id,(select id,CloseDate,amount from Opportunities),(SELECTClassification_Name__c FROM Classification_Type_Contacts__r) from contact];

If the problem still persists, please share the screenshot of relationship field.

Hope, it will help you....!!!!

If it helps you and kindly close your query by marking it as solved so that it can help others in the future. 

Thanks and Regards,