How to fetch the latest file in SOQL from each Order record

Question

I am querying files from the list of Order records through SOQL. I have added my SOQL query below for your reference.

I want to query only the latest file which begins with 'IO_' from each Order instead of querying all the files. I have added the LIKE clause in the WHERE condition of the query, but there can be multiple files under the Order record which can begin with the String 'IO_'

I am not sure how to query only the latest file from each Order instead of querying all the files associated with it. Can anyone please help me with this? Thanks in advance.

List<ContentDocumentLink> contLinks =  [SELECT Id, ContentDocumentId, ContentDocument.Title, ContentDocument.ContentModifiedDate, ContentDocument.LatestPublishedVersionId, ContentDocument.LatestPublishedVersion.VersionData, ContentDocument.LatestPublishedVersion.FileType FROM ContentDocumentLink WHERE LinkedEntityId in :Orders AND ContentDocument.Title LIKE 'IO_%'];

Answer 1

You can get the contentdocumentlinks from Order:

List<Order> orders =  [SELECT Id, (SELECT Id, ContentDocumentId, ContentDocument.Title, ContentDocument.ContentModifiedDate, ContentDocument.LatestPublishedVersionId, ContentDocument.LatestPublishedVersion.VersionData, ContentDocument.LatestPublishedVersion.FileType FROM ContentDocumentLinks WHERE ContentDocument.Title LIKE 'IO_%' ORDER BY CreatedDate DESC LIMIT 1) FROM Order WHERE Id IN :order];

List<ContentDocumentLink> links = new List<ContentDocumentLink>();

for (Order order : orders){
    if(!order.ContentDocumentLinks.isEmpty()) links.addAll(order.ContentDocumentLinks);
}