SOQL Query for Contacts (Related to the Account) & Opportunity Contact Roles - In a LWC (Lightning Web Component)

Question

For a given opportunity I would like a list of all contacts related to that opportunities Account & all of the opportunitycontactroles.

For example: if account a has three contacts and two of them have opportunity contact roles (for the opportunity that I am currently viewing)

Contact Name:     Opp Contact Role
Bob               Purchaser
Frank             Influencer
Mary              N/A or Null

This query would live on the the Opportunity record and be displayed via a LWC (lightning Web Component).

I have a working state querying All contacts from the Account onto the opportunity but would like to add that additional layer to show the opportunitycontactrole (if applicable)

I created a new Apex Class:

public class ContactList2 {
@AuraEnabled(cacheable=true)

public static List<contact> getContactList(string accountId, string opportunityId) {
    return [
        SELECT Id, Name, Phone, Email, MobilePhone, (SELECT Role FROM OpportunityContactRoles where OpportunityId =: opportunityId) 
        FROM Contact 
        WHERE AccountId =:accountId
    ];
}

}

Here is the JS LWC Call:

import { LightningElement, wire, api, track } from 'lwc';
// V2 w/ Contact Role Information
import getContactList2 from '@salesforce/apex/ContactList2.getContactList';

// Import Record Information
import { getRecord, getFieldValue } from 'lightning/uiRecordApi';

import ACC_ID from '@salesforce/schema/Opportunity.AccountId';
var accountId;


export default class ContactInformation extends NavigationMixin(LightningElement) {

    @api recordId; // Grab the Record Id
    @api objectApiName; // Grab the Objects API Name
    //@track columns = columns; // ASSIGN COLUMNS VAR

    @track accId;
    accountId;

    @wire(getRecord, {recordId: '$recordId', fields: [ACC_ID] })
    account({error, data}) {
        if(data) {

            this.accId = getFieldValue(data, ACC_ID);

            // Log Values for Account Id of Opportunity
            console.log("Account Id from Contact List:")
            console.log(this.accId);
            accountId = this.accId;
            console.log(accountId);

        } else if (error) {

            // Log Error
            console.log(error);
        }
    }

    @wire(getContactList2, {accountId: '$accountId', opportunityId: '$recordId' }) 
    contacts({error, data}){
        if(data) {
            console.log('Contact Query Data:');
            console.log(data);
        } else if(error) {
            console.log('Contact Query ERROR:');
            console.log(error);
        } else {
            console.log('Contact Query UNKNOWN')
        }
    }

When I log the return - I am getting the value of "Contact Query UNKNOWN". This makes me believe that there is an issue the APEX that I created for the custom data query. Would a list of "Contact" work as this is actually a combination of two objects of data?

I am able to run this query in the Query Editor in Dev Console and return data:

SELECT Id, Name, Phone, Email, MobilePhone, (SELECT Role FROM OpportunityContactRoles where OpportunityId = 'OPPORTUNITY ID') 
FROM Contact 
WHERE AccountId = 'ACCOUNT ID'

When doing the above Query I did not that the resilt of the OpportunityContactRoles came back as an array of an object, would this cause an issue? Example result in the column when data is present:

[{"Role":"Business User"}]

In addition, would the way I am passing the two record Ids (Account Id and Opp Id) from the LWC JS to the Apex cause an issue? Is that being done correctly?

Answer 1

For directly related contacts to Opportunities, you can access the Contact fields as well as the role within the subquery on OpportunityContactRoles when doing the SOQL on the Opportunity:

SELECT Id, Name, 
       (SELECT Id, ContactId, Contact.FirstName, Role FROM OpportunityContactRoles) 
  FROM Opportunity

I don't think there's a direct way with one SOQL query to get the other account contacts who are not associated with Opportunities.

Answer 2

I was able to resolve this by the following Query:

SELECT Id, Name, Title, Email, Mobilephone,
(SELECT Id, Role, IsPrimary from OpportunityContactRoles WHERE OpportunityId = '0060r000001e4FTAAY')
FROM Contact
WHERE AccountId = '0010r00000KdGoTAAV'

This provided all Contacts associated with the Account and if there was a Opportunity contact role for that Contact & Opportunity Id.