I am trying to fetch all files that are associated with the opportunities in 'XYZ' stage. I am trying to construct one nested query that would first fetch all the opportunities in that stage, then fetch Content Document Link and then in the last Content Version. Here is the query I am trying to construct:

            SELECT IsAssetEnabled, Checksum, ContentBodyId, ContentLocation, 
            ContentModifiedDate, Origin, ContentUrl, ContentDocumentId, Id, CreatedById,
            CreatedDate, Description, ExternalDataSourceId, ExternalDocumentInfo1, ExternalDocumentInfo2, FeaturedContentBoost, FeaturedContentDate, 
            FileExtension, SharingPrivacy, FileType, FirstPublishLocationId, IsDeleted, IsLatest, 
            LastModifiedById, LastModifiedDate, IsMajorVersion, NegativeRatingCount, OwnerId, PathOnClient, PositiveRatingCount, SharingOption, PublishStatus, RatingCount, 
            ReasonForChange, ContentSize, SystemModstamp, TagCsv, TextPreview, Title, ContentModifiedById, VersionData, VersionNumber **FROM ContentVersion** WHERE 
            ContentDocumentId IN (SELECT ContentDocumentId, Id, IsDeleted, LinkedEntityId, ShareType, Visibility, SystemModstamp FROM **ContentDocumentLink**
            WHERE LinkedEntityId IN('SELECT Id FROM **Opportunity** whereStageName='XYZ') AND LinkedEntity.Type='Opportunity')

I am getting the following error in the above query: ContentDocumentId IN (SELECT ContentDocumentId, Id, IsDeleted, LinkedEntityId

ERROR at Row:1:Column:719 unexpected token: ,**

  • I know I am using IN operator inappropriately, but I don't know how to fix the above query and make it work. – Anonymous Sep 27 '19 at 18:58
  • Is this the same question as this? salesforce.stackexchange.com/questions/279705/… Please do not post duplicate questions. especially since you seem to have omitted important details in this question. – David Cheng Sep 29 '19 at 16:32

You cannot do this in a single query because IN with a subselect only references the ID field of the subselection; you can't reference other ID fields in the object.

This means you have to collect all the content document IDs into a set by looping through a ContentDocumentLink query first, then you do another query on ContentVersion using the ID set.

Set<Id> contentDocIds = new Set<Id>();
for (ContentDocumentLink cdl : [SELECT ContentDocumentId FROM ContentDocumentLink WHERE LinkedEntityId IN (SELECT Id FROM Opportunity WHERE StageName = 'XYZ')]) {
List<ContentVersion> cvList = [SELECT Id FROM ContentVersion WHERE ContentDocumentId IN :contentDocIds];
| improve this answer | |
  • Thank you so much for the help. I will try that. – Anonymous Sep 30 '19 at 19:07
  • Is there a way I can display opportunity id in the list of content version to find out what opportunity is that file related to? I am returning the list of content version, so the user can see the file content but cannot see the opportunity id. – Anonymous Oct 2 '19 at 21:38
  • Sounds like you'll need to build a wrapper class to contain all the data that you need (ContentVersion record, opp ID and other fields, etc), then you can return a list of wrapper objects. – David Cheng Oct 2 '19 at 22:48
  • ok I need to create a REST API that would query all this data and return the records. I have the return type for REST API set up as application/json. Is it possible to return a list of wrapper objects(that has all the info) via the REST API? – Anonymous Oct 2 '19 at 23:12
  • Yes you can return the list in JSON format. – David Cheng Oct 3 '19 at 1:25

Not the answer you're looking for? Browse other questions tagged or ask your own question.