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