How can I prevent a ContentDocument being deleted when it is linked to a record meeting certain criteria?

Question

I have ContentDocuments being used to capture "proof" against certifications. Once the submitted certification (and "proof") has been reviewed, that certification is marked "Active". After this point I need to prevent deletion of the certification and its related ContentDocument.

Preventing the deletion of the certification is easy - that's just a before delete trigger process.

Preventing the deletion of the "proof" ContentDocument seems somewhat more tricky, but something I believe I should still do through a trigger on this object.

What I ideally want to do is have a before delete trigger on ContentDocument that performs a simple aggregate query through the ContentDocumentLink records associated with the ContentDocument records passed to the trigger, something like the following, to identify those that are in use by an "Active" certification:

List<AggregateResult> results = [
    SELECT COUNT(Id) certificationCount, ContentDocumentId
    FROM ContentDocumentLink
    WHERE ContentDocumentId IN :contentDocumentIds
          AND LinkedEntity.Type = 'Certification__c'
          AND LinkedEntity.Active__c = TRUE
    GROUP BY ContentDocumentId
];

Unfortunately, this SOQL is invalid, receiving an error:

Implementation restriction: filtering on non-id fields is only permitted when filtering by ContentDocumentLink.LinkedEntityId using the equals operator.

Clearly where there could be millions of active certification records there's no way I can sensibly pass in a list of these certifications' IDs.

Is there some way I could actually achieve this with SOQL? I'd be happy to use a couple of queries to do it if one cannot, as long as I don't have issues with other governor limits (query rows etc.).

Answer 1

It should be as simple as:

AggregateResult[] results = [
  SELECT COUNT(Id) recordCount, ContentDocumentId documentId
  FROM ContentDocumentLink
  WHERE ContentDocumentId IN :contentDocumentIds
    AND LinkedEntityId IN (SELECT Id FROM Certification__c WHERE Active__c = true)
  GROUP BY ContentDocumentId
];

While this looks really expensive (i.e. it looks like we're querying millions of certification records), Salesforce's Query Optimizer should be smart enough to first filter by ContentDocumentId, then by Certification, so it should return just the values you're interested in.