I'm having trouble creating a valid SOQL query. In one query I'd like to obtain fields from Contact, Account and AccountContactRelation.

It is for an Apex-backed Lightning Web Component that displays an account's list of related contacts in a datatable component. The component already works for a straight query of direct contacts.

For version 2, I now need a much more complex query that returns all contacts directly or indirectly related to an account, including contact fields, the contact's account id/account name, and the relationship's Roles.

(I'll also need to merge some fields that unfortunately overlap in Contact and AccountContactRole like Role__c and Roles, but that is not such a big issue.)

In one simple query using Account.Name I found it pulled the Account.Name from the AccountContactRelation, not the Contact, so it was wrong for indirect relations. I tried different kinds of joins in Developer Console but got a lot of errors, so decided to reach out to you.

For example this works but it seems to be in the wrong format to obtain AccountContactRelation.Roles:

select account.name, id, name from contact where id IN 
(select contactid from accountcontactrelation 
 where accountid = :acctid)

This does not work, but reflects what I want:

    AccountContactRelation.IsDirect, AccountContactRelation.Roles, 
        Contact.Id, Contact.FirstName, Contact.LastName, Contact.Phone, 
        Contact.AccountId, Contact.Account.Name, Contact.Title, 
        Contact.Email, Contact.Primary__c, Contact.Role__c
        FROM Contact)
FROM AccountContactRelation
    Contact.Id = AccountContactRelation.ContactId AND
    AccountContactRelation.AccountId = :acctid

You don't add another SELECT statement to join parent fields.

SELECT Account.Name, Contact.Name FROM AccountContactRelation

I figured it out, though this solution requires a lot of flattening in order to match the shape of LWC's datatable structure because I think SOQL does not allow aliasing. For example, Contact.FirstName => FirstName, and Contact.Account.Name => AccountName

Apparently the Developer Console just shows "Object" for Contact.Account.Name but this is valid SOQL.

List<AccountContactRelation> recs = [
  SELECT Contact.Id, Contact.FirstName, Contact.LastName, 
    Contact.Phone, Contact.Account.Id, Contact.Account.Name,
    Contact.Title, Contact.Email, Contact.Primary__c, 
    Contact.Role__c, IsDirect, Roles
  FROM AccountContactRelation 
  WHERE AccountId = :acctid 
  ORDER BY Contact.LastName                

This is returned to JavaScript:

    "Id":"07k0l000007mqkiAAA",  <— AccountContactRelation id
        "LastName":"\"Speedy\" O'Toole",
        "Title":"Sales Planning",
        "Role__c":"Mark Local Role",
            "Name":"Ursa Major Investments 2"

FYI here is the flattening I did (perhaps next time I would try an automatic concatenating / reducing function).

// The data received from Apex comes into this.contactData.
// This clones since the original is immutable. Not sure if we can just add items to an immutable object..

enrichContactData() {
 let cloneData = [];
 if (this.contactData) {
   for (let row of this.contactData) {
     let newrow = Object.assign({}, row); // Shallow copy (level 2 refers to original)
     newrow = {
       FirstName: row.Contact.FirstName,
       LastName: row.Contact.LastName,
       Phone: row.Contact.Phone,
       Title: row.Contact.Title,
       Email: row.Contact.Email,
       Primary__c: row.Contact.Primary__c,
       Role__c: row.Contact.Role__c,
       AccountName: row.Contact.Account.Name
   } // for each row
   this.contactData = cloneData;   
 } // if this.contactData

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.