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
satchsatch 

Grab related objects from SOQL?

I'm trying to grab all the notes for every contact associated with an account. I built a controller to do it:

 

public class aggregateNotes{
    private Opportunity opp;
    public aggregateNotes(ApexPages.StandardController controller) {
        this.opp = (Opportunity)controller.getRecord();
    }
    
    // container for the notes (not used, yet)
    public class row {
        public string LastName {get; set;}
        public string Body {get; set;}
        public row(sObject o){
            LastName = (String)o.get('LastName');
            Body = (String)o.get('Body');
        }        
    }
    
    public List<row> getRelatedNotes(){
        // get the account id from the opportunity
        opp = [SELECT id, Account.id FROM Opportunity WHERE id = :opp.id];
        
        List<row> rows = new List<row>();
        for(sObject o : [SELECT LastName, (SELECT Body FROM Notes) FROM Contact WHERE Account.Id = :opp.Account.id]){
            // this fails! WTF?
            System.debug(o.get('Body'));
        }
        
        return rows;
    }
}

 

I get a "System.SObjectException: Invalid field Body for Contact" on the System.debug(o.get('Body')) call. How do I access the body field in that SOQL?


Best Answer chosen by Admin (Salesforce Developers) 
Eugene PozniakEugene Pozniak

You try to get access to the field 'Body' of Contact, but it's Note's field. Modify your code like this:

 

for(Contact contact : [SELECT LastName, (SELECT Body FROM Notes) FROM Contact WHERE Account.Id = :opp.Account.id]){
    if(!contact.Notes.isEmpty()) {
        for(Note contactNote : contact.Notes) {
            System.debug(contactNote.Body);
        }
    }
}

 

 

All Answers

AdrianCCAdrianCC

Hi!

 

First, I don't think there's a Notes standard object in SFDC. You should be using Note.

 

Secondly, I see you're going for maximum efficiency in your code, trying to get as much done in a few lines. Great! but when it comes to debugging it complicates things. Try to split your code into smaller steps, like having 2 sepparate soqls etc.

 

Third, you're getting that error cause the SELECT Body FROM Notes part retrieves a list of records, not individual fields. You would need to itterate through that list and show the Body field. There's a level that's missing from the relationship, to put it in a simple way, O.Body should be smth like O.<Note>.Body...

 

My solution would be like this:

public List<Note> getRelatedNotes(){
    // get the account id from the opportunity
    opp = [SELECT Id, AccountId FROM Opportunity WHERE Id = :opp.Id];

    //get a set of all the related Contact Ids
    Set<String> contactIdsSet = new Set<String>();
    List<Contact> relatedContactsList = [SELECT Id FROM Contact WHERE AccountId = :opp.AccountId];
    if ((relatedContactsList != null) && (relatedContactsList.size() > 0)) {
	for (Contact c: relatedContactsList) {
        	contactIdsSet.add(c.Id);
	}
    } else return null;
	
    //retrieve all the notes related to the contacts in the set
    if ((contactIdsSet != null) && (contactIdsSet.size() > 0)) {
	List<Note> relatedNotesList = [SELECT Id, Body FROM Note WHERE ContactId IN :contactIdsSet];
	System.debug('relatedNotesList has this value: ' + relatedNotesList);
	return relatedNotesList;
    } else return null;
}

 Once you have the list of related notes you can create the container(wrapper) list and use it in your page.

 

Hope this helps :),

Adrian

Eugene PozniakEugene Pozniak

You try to get access to the field 'Body' of Contact, but it's Note's field. Modify your code like this:

 

for(Contact contact : [SELECT LastName, (SELECT Body FROM Notes) FROM Contact WHERE Account.Id = :opp.Account.id]){
    if(!contact.Notes.isEmpty()) {
        for(Note contactNote : contact.Notes) {
            System.debug(contactNote.Body);
        }
    }
}

 

 

This was selected as the best answer