SOQL query for Files & Notes Related List

filessoql

I'm a new developer who needs help with a SOQL query. As you know, the standard files and notes related lists on an Account do not include files/notes added on child objects (e.g., Opportunity, Contact, etc.). I have a requirement to create an LWC related list which does include the Account and child objects' files/notes.

I have created an apex class with queries that accomplish this requirement. However, I can't figure out who to include the linkedentityid in the query on the contentversion. I know there are limitations with querying the contentdocumentlink and displaying on the contentversion. Does anyone know how to include the linkedentityid? Basically, the request is to show the file/note and the record where it is related.

Thank you so much for the time and assistance!

LWC related list

public without sharing class FilesRelatedList {
    @AuraEnabled(cacheable = true)
    
    public Static  List<ContentVersion> cvRecords(String recId){

        //Create variable to hold Ids of Account and related Contacts, Asks, Tasks and Drive Opps
        set<Id> setIds = new Set<id>();

        //Retrive Ids of Account and related Contacts, Asks, Drive Opps and Tasks
            list<Account> lstAccount = [SELECT Id FROM Account WHERE Id = :recId];

            list<Contact> lstContact = [SELECT Id FROM Contact WHERE AccountId = :recId];

            list<Opportunity> lstOpportunity =[SELECT Id FROM Opportunity WHERE AccountId = :recId];

            list<Drive_Opportunity__c> lstDriveOpp =[SELECT Id FROM Drive_Opportunity__c WHERE Account__c = :recId];

            list<Task> lstTask = [SELECT Id, WhoId, WhatId FROM Task WHERE WhatId = :recId OR WhatId IN :lstOpportunity 
                                  OR WhatId IN :lstDriveOpp OR WhoId IN :lstContact];

        //Add the Ids from the above to the setIds variable
            for(Account acc : lstAccount){
                setIds.add(acc.Id);}
            for(Contact con : lstContact){
                setIds.add(con.Id);}
            for(Opportunity opp : lstOpportunity){
                setIds.add(opp.Id);}
            for(Drive_Opportunity__c driveopp : lstDriveOpp){
                setIds.add(driveopp.Id);}
            for(Task tsk : lstTask){
                setIds.add(tsk.Id);}

        
        //Retrieve all ContentDocumentLinks with a LinkedEntityId matching any Id in the setIds variable above
       List<ContentDocumentLink> files = [SELECT ContentDocumentId, LinkedEntityId, ContentDocument.LatestPublishedVersionId, 
            ContentDocument.FileExtension, ContentDocument.Title, ContentDocument.ContentModifiedDate, LinkedEntity.Name, 
            ContentDocument.OwnerId, ContentDocument.Owner.FirstName, ContentDocument.Owner.LastName
            FROM ContentDocumentLink WHERE LinkedEntityId = :setIds];

               
      
                return files;
    }
}

Best Answer

I think you have 2 main options here:

  1. instead of showing a list of ContentVersion, create a wrapper class in which you combine data from ContentDocumentLink and ContentVersion. Basically build a Map<Id,WrapperClass> where the Id is the ContentDocumentId. You then populate the records in the map while going through the results of the 2 queries.

  2. make use of the lesser-known 'LatestPublishedVersion' relationship on the ContentDocument object when querying the ContentDocumentLink. This relationship takes you directly to the latest ContentVersion record for a ContentDocument and it does allow the inclusion of relationship fields.

Example query (to be extended with all further fields you need):

SELECT  ContentDocument.LatestPublishedVersionId, ContentDocument.LatestPublishedVersion.FileExtension, ContentDocument.LatestPublishedVersion.Title
FROM ContentDocumentLink 
WHERE LinkedEntityId = :setIds

As far as I can assess, option #2 seems the more attractive and less complex one for this case.

Related Topic