How to fetch contentNote records as per the contact record?

Question

I am trying to fetch the note title, note body as per the related contact ID in one SOQL query as two SOQL queries won't serve the purpose.

In contentNote, We have the note title and note body but don't have the contact information. For contact information, there is contentDocumentLink but contentNote don't share any direct relation with contentDocumentLink.

When I run the below SOQL query in the Developer Console

SELECT Id, Title, TextPreview FROM ContentVersion 
WHERE ContentDocumentID IN (SELECT ContentDocumentId FROM ContentDocumentLink WHERE LinkedEntityId = '0030H000059CBKAQA4') 

It throws below error:

ERROR at Row:2:Column:59 Entity 'ContentDocumentLink' is not supported for semi join inner selects

below query also don't serve the purpose as contentDocument object has note title but don't have the note body.

SELECT ContentDocument.title FROM ContentDocumentLink WHERE LinkedEntityId
= '0030H000059CBKAQA4'

It is really frustrating to get all the details in one query, could someone please suggest if note title, body and contact id can be fetched in a single query

Our purpose is to get all the data and integrate with another system.

Answer 1

There exists a relation between ContentNote and ContentDocumentLink. In order to get the ContentNote or ContentVersion, there are different ways of achieving it.

Using 2 SOQL's

• You will have to first query on ContentDocumentLink using the LinkedEntityId i.e. in your case it will be ContactIDs and collect all the ContentDocumentId's.
• Then after getting the ContentDocumentID's, you can query on ContentNote using the ContentDocumentId's retrieved from previous query.

This is how your sample code should look like

Set<Id> contactIds= new Set<Id>(); 
contactIds.add('0030H000059CBKAQA4'); //include all the contact ids for which you need to retrieve content note
Set<Id> contentDocumentIds = new Set<Id>();
//get all contentdocumentids so that you can query on contentnote
for(ContentDocumentLink conDoc : [SELECT ContentDocumentId FROM ContentDocumentLink WHERE LinkedEntityId IN :contactIds]){
    contentDocumentIds.add(conDoc.ContentDocumentId);
}
//get all the content notes using contentDocumentIds
List<ContentNote> contentNotes = [SELECT Id, Title, Content from ContentNote where Id In: contentDocumentIds];

Using relationship queries

• You can get the ContentNote details using relationship fields by querying on ContentDocumentLink object and filtering it with LinkedEntityId

Your sample code for that would be

Set<Id> contactIds= new Set<Id>(); 
contactIds.add('0030H000059CBKAQA4'); //include all the contact ids for which you need to retrieve content note
List<ContentDocumentLink> conLinks = [select Id, LinkedEntityId,ContentDocumentId, ContentDocument.LatestPublishedVersion.Title,ContentDocument.LatestPublishedVersion.VersionData from ContentDocumentLink where LinkedEntityId IN :contactIds];
system.debug(conLinks); //this will give only fields from ContentDocumentLink
system.debug(conLinks[0].LinkedEntityId); //this will give LinkedEntityId, in your case it will be Contact's ID
system.debug(conLinks[0].ContentDocument.LatestPublishedVersion.Title); //to access ContentNote's title
system.debug(conLinks[0].ContentDocument.LatestPublishedVersion.VersionData); //to access ContentNote's VersionData

In the above query, you can get the ContentNote's fields i.e. Title and VersionData along with its LinkedEntityId which would be your ContactId

You can refer the API document of Content Note for more information.

Answer 2

According to ContentNote Data model, ContentDocumentLink holds the reference of ContentNote through ContentDocumentId and Related Records of Contact through LinkedEntityId.

If you describe those ContentNote and ContentDocumentLink objects you wont see any relationship between them.

Here, you need to create two separate queries.

Also, why extra query is creating the issue here still unclear from your question. There could be many approaches to solve a problem.

Answer 3

You can retrieve all Notes for a parent record by querying on ContentDocumentLink and ContentNote objects.

A ContentDocumentLink record is created for each ContentNote entry. ContentDocumentId field in ContentDocumentLink object has a reference to ContentNote.

Here is the code to fetch all the SNotes related an account.

 public static List<ContentNote> getNotesForAccount(Id accountId) {
    List<ContentNote> contentNotes = new List<ContentNote>();
    
    List<ContentDocumentLink> contentDocumentLinks = [SELECT ContentDocumentId FROM ContentDocumentLink WHERE LinkedEntityId = :accountId];
    
    if (!contentDocumentLinks.isEmpty()) {
        Set<Id> contentDocumentIds = new Set<Id>();
        for (ContentDocumentLink contentDocumentLink : contentDocumentLinks) {
            contentDocumentIds.add(contentDocumentLink.ContentDocumentId);
        }
        
        contentNotes = [SELECT Id, Title, Content from ContentNote WHERE Id IN :contentDocumentIds];
        
    }
    
    return contentNotes;
}