Hard to explain, but we have opportunities of many different types. I need to only get opportunities of a specific type, which i have done. Now I need to only get projects that have opportunities of that type. There is no direct relationship between projects and opportunities. Project is the parent of property and property is the parent of opportunity.

So i also had to return a list of properties that have opportunities of a specific type.(first query. child to parent, works fine)

And i have to return a list of projects with opportunities of a specific type.(second query. child to grandparent)

public static List <Project__c> searchProject(String searchString) 
   String searching = searchString + '%';
   List<Property__c> prop = [SELECT id, name from Property__c where name Like: 
                             searching and Id in (Select Property__c FROM 
                             opportunity Where recordtype.name in ('Test')
                             and Probability not in (0,5,100)) order by Name Limit 10];

   List <Project__c> returnList = new List <Project__c> ();
   List <Project__c> lstOfProjects = [SELECT id, name from Project__c where name 
                                      Like: searching and Id in 
                                      (Select Property__r.Project_Name__r.Name FROM 
                                      opportunity Where recordtype.name in ('Test') and 
                                      Probability not in (0,5,100)) order by Name Limit 10];

    for (Project__c acc: lstOfProjects) 
      return returnList;     
  • 2
    What exactly are you trying to do? Your question is a bit confusing. – sfdcfox Sep 12 '19 at 21:36
  • 1
    for any given FROM object, you can return fields from (grand, greatgrand,...) parents or filter on fields from (grand, greatgrand,...) parents. You need to explain your schema - use edit – cropredy Sep 12 '19 at 22:13
  • I have not seen a way to do this. I've tried quering from opportunities and that works fine but i cant display a list of projects. i would only be able to display one project with [select project_name__c from opportunity].Project_name__c; – Tyler Sep 13 '19 at 20:26
  • what is your schema? – cropredy Sep 13 '19 at 23:05

If you're objects are truly setup as you state (Property is a parent of Project and Project is a parent of Opportunity), can you not simply query the Opportunity and use parent references to filter the Properties? Perhaps something along these lines...

  FROM Opportunity
 WHERE RecordType.Name IN ('Test')
   AND Probability NOT IN (0,5,100)
   AND Property__r.Project__r.Name LIKE :searching

Here's an example of how you can use the results of a child-parent-grandparent query... this is obviously a different set of objects than yours... but the concept is the same. Not knowing what you want to actually do with your output, I just threw a couple options in. Make sure that whatever you do with the output handles any duplicates that may occur, since one grandparent can exist on more than one child or grandchild.

public static void childToParentExample() {

        // Create a map to hold the Account Id and Account SObject.
        Map<Id, Account> accountMap = new Map<Id, Account>();
        Set<String> accountNameSet = new Set<String>();

        // Execute a child-to-parent query and loop through the results.
        for (OpportunityHistory oli: [SELECT Id,
                                      FROM OpportunityHistory
                                      WHERE StageName = 'Closed Won'
                                      AND Opportunity.Name LIKE '%Test%']) {

            // Add the Accounts to the map. Ignore records where the Account doesn't exist.
            if (oli.Opportunity.Account.Id != null) {

            // Add the names of the Accounts to a set.


        // Output the map.

        // Output the set.
| improve this answer | |
  • I've tried that and it will return a list of Opportunities and not Projects because the query is running on opportunity. Unless there is a way around this im not familiar with – Tyler Sep 16 '19 at 14:00
  • Tyler, what is the output you need? Once you execute the child-parent query, you can loop through the list of Opportunities and build a Set or Map of the Projects that are associated with each of the Opportunities. If you include all of the fields that you need in your query, you shouldn't have to perform a second query. – Chris Johnson Sep 16 '19 at 15:34
  • I tried to do that and was unable to get it to work. Could you provide an example? – Tyler Sep 16 '19 at 15:47
  • I just edited my response to include an example of dealing with the child-parent-grandparent query result. – Chris Johnson Sep 16 '19 at 17:08
  • I just noticed that your method is void. I'm not that familiar with maps or how they work. But to get a callback from the controller/helper doesn't your method have to return something? I don't need to do anything with this query other than display the results in a lightning component.I just can't figure out how to do this. Or if its even possible without making a relationship directly to each other – Tyler Sep 17 '19 at 15:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.