How to fetch Contact and Case data with LiveChatTranscript using SOQL Lookup relationship

Question

I have Contact and Case relationship mapped to LiveChatTranscript through ContactId and CaseId like the following:

I need to fetch LiveChatTranscript data along with Contact and Case in a single query. From documentation: I have tried the following query:

SELECT Id,  (SELECT Contact.FirstName from Contacts), UserAgent, ChatKey, ChatDuration, Status, Body, CreatedDate, LastModifiedDate FROM LiveChatTranscript WHERE Status = 'Completed' LIMIT 20

But always getting

[ { "message": "\n(SELECT Contact.FirstName from Contacts), UserAgent, ChatKey, ChatDuration\n ^\nERROR at Row:1:Column:44\nDidn't understand relationship 'Contacts' in FROM part of query call. If you are attempting to use a custom relationship, be sure to append the '__r' after the custom relationship name. Please reference your WSDL or the describe call for the appropriate names.", "errorCode": "INVALID_TYPE" } ]

I'm confused how am I supposed to use the ContactId and CaseId for this case since it says there is not relationship like what the documentation specified. Any help is greatly appreciated!

Answer 1

As per the documentation, the LiveChatTranscript includes a lookup field, "ContactId", to the single contact involved in the chat. The relationship, following standard Salesforce naming conventions, will thus be "Contact". You simply need to adjust your query as follows:

SELECT Id, Contact.FirstName, UserAgent, ChatKey, ChatDuration, Status, Body, CreatedDate, LastModifiedDate
    FROM LiveChatTranscript WHERE Status = 'Completed' LIMIT 20

There is no need to use a nested query here (there isn't a one to many relationship from live chat transcript to contacts).

To access the data once retrieved you simply need to apply that same path, Contact.FirstName.