Help with sorting content documents by custom field on its LinkedEntity

Question

This SOQL statement works, but I'm trying to sort the Content Documents by a field e.g. Sort_Order__c on Invoice__c.

SELECT ContentDocument.id,LinkedEntity.Name,LinkedEntityId
FROM ContentDocumentLink 
WHERE LinkedEntityId IN (
     SELECT Id
     FROM Invoice__c
     WHERE Funding_Order__c = 'XXXXXXXXXXXXXXXXXX'
)

We need to pull together a funding order where each invoice has one or more related files and they need to be in a certain sort order.

Is this possible in a single SOQL query? I was hoping to use the query in Conga Composer.

Answer 1

How about making a trigger (or a flow if possible) that inserts a special join record when contentdocumentlink is created that has a relationship to the sobject and the contentdocumentlink record.

This way you have all the related records available via parent relationships, and your sort will work.

SELECT Parent_CDL__c, Parent_Entity__c, Parent_Entity__r.Name 
FROM CDL_Record_Join__c
WHERE Parent_Entity__c IN (SELECT Id FROM Invoice__c WHERE Funding_Order__c = 'XXXXXXXXXXXXXXXXXX')
ORDER BY Parent_Entity__r.Sort_Order__c

(All above objects and relationships are hypothetical of course)

Actual solution as added by OP: (unable to use actual relationship queries, but that didn't matter in this case)

Solution is a custom junction object that can store info that wasn't possible on using the standard Salesforce ContentDocumentLink object. The custom junction object that works has a text field that stores the ContentDocument record Id, and Funding_Draw__c (Master-Detail), Invoice_Item__c (Master-Detail) fields. This allows me to create a Conga query that sorts correctly as follows:

SELECT ContentDocument_Id__c 
FROM Invoice_Item_Content_Doc_Link__c 
WHERE Funding_Draw__c = '{pv0}' ORDER BY Invoice_Item__r.Draw_Sort_Order__c

Alternative Approach

How about still using a Trigger/Flow and putting the Sort_Order__c in the ContentDocument.Description field.

Then you can sort like this:

SELECT ContentDocument.Id,LinkedEntity.Name,LinkedEntityId
FROM ContentDocumentLink 
WHERE LinkedEntityId IN (
     SELECT Id
     FROM Invoice__c
     WHERE Funding_Order__c = 'XXXXXXXXXXXXXXXXXX'
)
ORDER BY ContentDocument.Description ASC