Return a custom List of Contacts & Opportunity Contact Roles from Account

Question

I am attempting to query a list of Contacts (associated to the account) & OpportunityContactRoles (if applicable). So when a user is on an Opportunity Record they are able to see a full list of contacts associated to the account & if there is an opportunityContactRole for that contact and opportunity.

I was able to query the appropriate records with the following:

SELECT Id, Name, Title, Email, Mobilephone,
(SELECT Id, Role, IsPrimary from OpportunityContactRoles WHERE OpportunityId = OPPORTUNITYID)
FROM Contact
WHERE AccountId = ACCOUNTID

When I run this directly into the query editor is works and returns the data as expected, but when I call this in an apex class it is only returning the Contact Information, not the additional OpportunityContactRole Information. I believe this is because I am referencing 'Contact' list in the Apex and the additional fields don't exist on that object. So I would have to create a custom method for this data set.

• Is the best way to return the combined data set with a custom method?
• If so, what would that look like?
• Is there a more efficient way to approach this?
• Change Contact to Object? Which works...
• Is there a best practice for this?

APEX:

    @AuraEnabled(cacheable=true)
    public static List<Contact> getOpportunityContacts(string accountId, string opportunityId) {
        String account = accountId;
        String opportunity = opportunityId;
        return [
            SELECT Id, Name, Title, Email, Mobilephone,
            (SELECT Id, Role, IsPrimary from OpportunityContactRoles WHERE OpportunityId =:opportunity)
            FROM Contact
            WHERE AccountId =:account
        ];
    }
}

Answer 1

It's as you mentioned, you're returning a Contact list which will only contain just that...a list of contacts. You need to handle the information from the subquery for OpportunityContactRole to return it.

You could use a map as shown below

@AuraEnabled(cacheable=true)
public static Map<Contact,OpportunityContactRole> getOpportunityContacts(string accountId, string opportunityId) {

    Map<Contact,OpportunityContactRole> contactOppRole = new Map<Contact,OpportunityContactRole>();
    List<Contact> contacts = [SELECT Id, Name, Title, Email, Mobilephone,
    (SELECT Id, Role, IsPrimary from OpportunityContactRoles WHERE OpportunityId =: 
    opportunityId)
    FROM Contact WHERE AccountId =:accountId];

    for(Contact con: contacts){
        contactOppRole.put(con, con.OpportunityContactRoles);
    }

    return contactOppRole;

A lot of your questions depend on what this is solving. Do you need the OpportunityContactRole info? If yes, I don't see anything wrong with the above.

You could also look at creating your own class to allow yourself to be able to combine info from both objects and just return a list that contains both information. Below just shows an example (typed in freely so it might not compile as is). I'm not sure this situation warrants this.

public class ContactOppRoleWrapper {
    public Contact contactInfo {get;set;}
    public OpportunityContactRole oppRole {get;set;}

    public ContactOppRoleWrapper(Contact con, OpportunityContactRole conOppRole){
        contactInfo = con;
        oppRole = conOppRole; 
    }

}

List<ContactOppRoleWrapper> contactWrapper = new List<ContactOppRole>();
for(Contact con: contacts){
      contactWrapper.add(new ContactOppRoleWrapper(con, con.OpportunityContactRoles));
}

return contactWrapper;

You can also check out Derek F's answer in this question for a deeper background regarding subqueries and accessing the variables.