1

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:

SELECT 
    AccountContactRelation.IsDirect, AccountContactRelation.Roles, 
    AccountContactRelation.IsActive, 
    (SELECT 
        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
WHERE
    Contact.Id = AccountContactRelation.ContactId AND
    AccountContactRelation.AccountId = :acctid

2 Answers 2

2

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

SELECT Account.Name, Contact.Name FROM AccountContactRelation
0

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:

[
 {
    "ContactId":"0030l00000YceeBAAR",
    "IsDirect":true,
    "Id":"07k0l000007mqkiAAA",  <— AccountContactRelation id
    "Contact":{
        "Id":"0030l00000YceeBAAR",
        "FirstName":"Mark",
        "LastName":"\"Speedy\" O'Toole",
        "Phone":"+1-212-667-8888",
        "AccountId":"0010l000014BZgyAAG",
        "Title":"Sales Planning",
        "Email":"[email protected]",
        "Primary__c":true,
        "Role__c":"Mark Local Role",
        "Account":{
            "Id":"0010l000014BZgyAAG",
            "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 = {
       ...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
     };                
     cloneData.push(newrow);
   } // for each row
   this.contactData = cloneData;   
 } // if this.contactData
}

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .