0

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.

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.

  • Thank you so much for responding but in the last query(contentNotes), we will get the notes but how we will make sure which note is related to which contact without using inner loops? – gs650x May 29 at 5:22
  • @gs650x Updated the answer with another approach, that should help you solve your problem. – Vijay Ganji May 29 at 7:07
  • Thank you again, I am exactly looking for this solution but this query is not returning expected results – gs650x May 29 at 7:29
  • Can you please elaborate more on what are you expecting and what are you getting by the above query, so that I can see if I can help you on that. – Vijay Ganji May 29 at 7:32
  • I was expecting LinkedEntityID(contactID), noteTitle and note Discreption, I ran the above query and passed a contactId has a note title and note body and when I ran the query in apex query return 2 fields id, linkedEntityID and didnot return rest field and when I ran the same in query editor it retun 3 fields one is id, then linkedEntityID and instead of note title and body I have got 3rd field named ContentDocument.LatestPublishedVersion no title not version data – gs650x May 29 at 8:09
0

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.

ContentNote

  • Thank you so much for responding but in two queries how I will match the contact IDs with the note title and note body without using inner loops? – gs650x May 27 at 15:01

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.