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
Michael3.BrownMichael3.Brown 

I Can Use SOQL to pull fields from 2 related objects, but can I do it with 3 or more?

Alright, I have 3 objects set up.

A custom Pipeline Tracker object used to track certain deals

The standard Account Object

A custom Account Team object

 

Both my Pipeline Tracker and Account Team custom objects relate to the Account object on the Account Name.  I need to set up an SOQL statement pulling data from all the objects. About 90% will come from the Pipeline Object and only a handful of fields will come from the Account and Account Team objects.

 

As a test, I've been trying to call 3 fields. One field from each record.

I can successfully call "Category" from the Pipelline Tracker object, and I can successfully relate my Pipeline Tracker to the Account Object and pull the field "Sales Pole" from the Account Object. However, I'm stuck at how to then relate the Account object to my Account Team object and pull the field, "Sales Director". Does anyone know the proper syntax to successfully call a related record of a related record?

 

    List<Pipeline_Tracker_3__c> pipelineRecords = new List <Pipeline_Tracker_3__c>();

    public MultipleDBJoin()
    {
        buildList();
    }

    public void buildList()
    {
        for (Pipeline_Tracker_3__c pt: [SELECT Category__c, Account__r.Sales_Pole__c, Account__r.Account_Team__r.SD__c
        FROM Pipeline_Tracker_3__c])
        {
            pipelineRecords.add(pt);
        }
    }

 

Thanks,

Mike

Best Answer chosen by Admin (Salesforce Developers) 
Jake GmerekJake Gmerek
account a = new account();
     a = [select id, (select id from Pipeline_Tracker_3s__r), (select id from Account_teams__r) from order_detail__c where id = 'some id' LIMIT 1];

 The Child relationship (inner from clauses) names may be different, but this should get you started.

All Answers

Jake GmerekJake Gmerek
account a = new account();
     a = [select id, (select id from Pipeline_Tracker_3s__r), (select id from Account_teams__r) from order_detail__c where id = 'some id' LIMIT 1];

 The Child relationship (inner from clauses) names may be different, but this should get you started.

This was selected as the best answer
Michael3.BrownMichael3.Brown

Hi Jake,

 

Thanks for the response. I'm going to try and play around with this.

 

 

I had a question, though

It looks like you changed my Pipeline Tracker API from Pipeline_Tracker_3__c to Pipeline_Tracker_3s_r. What does the s__r mean?

 
Jake GmerekJake Gmerek

When you create a lookup or master-detail relationship there is a field called Child Relationship Name.  That is what you are looking to use.  Generally it is populated with a plural version of the object name so that is where I got the 's' from.  The '__r' is sort of like the '__c' except instead of indicating a custom field, it indicates a relationship of some sort.  When you play with it, you will want to look at the relationship fields on the child objects, find the Child Relationship Name and add the '__r' to it.

 

This article explains the concept in detail:

 

http://wiki.developerforce.com/index.php/A_Deeper_look_at_SOQL_and_Relationship_Queries_on_Force.com

 

 
Michael3.BrownMichael3.Brown

 

Thanks Jake, I played around with the nested Select Statements and I believe I got a working SQL Statement.

 

However, would you know how to display this on my VF page? Usually, I call the SOQL Statement within a for Loop so I can add each individual record to a  list and then call the List records on my VF page. However, when declaring a List, you have to associate it with an object, such as Account or my Pipeline_Tracker__c. I defined my List as an Account type, but I am unable to call my fields "Category__c" and "SD_c" as they are not on the account object.

    List <Account> pipelineRecords = new List <Account> ();
                
        for (Account acct: [SELECT Sales_Pole__c,  
            (SELECT SD__c FROM Account_Teams__r), (SELECT Category__c FROM Pipeline_Tracker_3__r)       
            FROM Account])
        {
            pipelineRecords.add(acct);
        }

 

 

 Is there a way to properly add these items to a list and call that list on my VF page?

 

Thanks for all the help,

Mike
Ralph CallawayRalph Callaway

You can loop over the categories like so

// loop over accounts
for(Account pipelineRecord : pipelineRecords) {
  // loop over tracker
  for(Pipeline_Tracker_3__c tracker : pipelineRecord.Pipeline_Tracker_3__r) {
    // do stuff with tracker
  }
}

 In visualforce you can use a repeat tag

<apex:repeat value="{!pipelineRecords}" var="pipelineRecord">
  <apex:repeat value="{!pipelineRecord.pipeline_tracker_3__r}" var="pipelineTracker">
   Category: {!pipelineTracker.category__c}
  </apex:repeat>
</apex:repeat>