SOQL Subquery - Getting Cases that have updated ContentDocumentLinks (Attachments)

Question

I have an integration that pulls cases using the following query. This works well.

SELECT id, casenumber FROM case 
  WHERE LastModifiedDate >= 2022-02-21T22:08:01Z AND LastModifiedDate <= 2022-02-22T16:39:04Z AND 
        RecordTypeId IN ('01241000001cms6AAA') 
ORDER by casenumber desc

Later in the integration we ask if any of these cases from the query above have attachments (ContentDocumentLink) that have also been updated with the following query.

Select ContentDocumentId, Visibility From ContentDocumentLink
    Where LinkedEntityId = '5008C000001uljWQAQ' And
          SystemModstamp  > 2022-02-21T22:08:01Z

The issue here is that if an attachment is updated the Case lastupdated date is updated only on the attachment. This causes us to have to update something on the case as well.

Ideally we would like to know in our first query if the case has an updated ContentDocumentLink.

I have taken a handful of attempts at merging these queries but have not been successful. Is this even possible and does anyone have experience with trying to do something similar?

Thanks,

Answer 1

What you cannot do is filter down the Cases returned in the 1st query on only Cases that have a modified ContentDocumentLink.

E.g. this will result in an error:

select id from Case where Id in (select linkedentityId from contentdocumentlink where SystemModstamp  > 2022-02-22T13:32:47Z)

Entity 'contentdocumentlink' is not supported for semi join inner selects

What you can do is fetch the updated ContentDocumentLinks while fetching the Cases (but you will also get Cases that do not have an updated ContentDocumentLink):

select id, (select id,SystemModstamp   from Contentdocumentlinks where SystemModstamp  > 2022-02-22T13:32:47Z) from Case

You can then loop through the Case.ContentDocumentLinks list in your apex code to find out if there are any updated ContentDocumentLinks.

The key here is to find the right relationship name from which you can query child ContentDocumentLinks from the parent Case object. In this case (pun intended) ContentDocumentLinks