How to query ContentDocument and Custom object's fields at once?

Question

We have 3 objects.

1. OS_Account_Document__c (Custom)
2. ContentDocument
3. ContentDocumentLink

I need to query two custom fields (Document_Type__c and Custom_Document_Description__c) from the Custom Object OS_Account_Document__c and three Standard fields (Id, Title, ContentSize) from ContentDocument.

The Id of the Custom Object is stored in the LinkedEntityId field of ContentDocumentLink. ContentDocumentLink also has a ContentDocumentId field which stores Id of the ContentDocument.

So, for every custom object record, I need to have Document_Type__c and Custom_Document_Description__c (Custom object fields) and it's corresponding Title, ContentSize and Id (from ContentDocument object) in one SOQL query.

I tried something like this but it didn't work.

SELECT Id, Title, ContentSize FROM ContentDocument WHERE Id in 
      (SELECT ContentDocumentId FROM ContentDocumentLink WHERE LinkedEntityId in 
             (SELECT Id FROM OS_Account_Document__c where Account__c='0015B00000fSGQ3QAO'))

Answer 1

You can do it in one query if you start from the Custom Object and utilize the ContentDocumentLinks relationship in a subquery.

Here's an example with Account as the main object:

SELECT Name, 
      (SELECT ContentDocumentId, ContentDocument.Title, ContentDocument.ContentSize 
       FROM ContentDocumentLinks) 
FROM Account

Note that the relationship is one-to-many, so you'll have a ContentDocumentLinks list on your resulting objects, typed as a List<ContentDocumentLink>. You could access the data like this:

for (Account a: [SELECT Name, (SELECT ContentDocumentId, ContentDocument.Title, ContentDocument.ContentSize FROM ContentDocumentLinks) FROM Account]) {
    for (ContentDocumentLink cdl: a.ContentDocumentLinks) {
        System.debug('Linked Document Id: ' + cdl.ContentDocumentId);
        System.debug('Linked Document Title: ' + cdl.ContentDocument.Title);
        System.debug('Linked Document Size: ' + cdl.ContentDocument.ContentSize);
    }
}