1

I'm trying to create a SOQL query that pulls records from an Object based on if there are any results from another linked object. Here is sort of what I am trying to do.

SELECT id, casenumber FROM case WHERE count(SELECT ContentDocumentId FROM ContentDocumentLink WHERE  LinkedEntityId = case.id) > 0

This returns the error, MALFORMED_QUERY: casenumber FROM case WHERE count(SELECT ContentDocumentId FROM ContentDocumentLink ^ ERROR at Row:1:Column:44 unexpected token: 'SELECT'

After a few attempts at trying to make this work I am not sure what else try. I know SOQL has some limitations but I am unsure if this is one of them. Anyone have insight? Thanks.

Bryan Harrington
  • 991
  • 2
  • 17
  • 31

1 Answers1

1

It's "down, then up", via many to many relationship. 1 document could be linked to many Cases but wasting storage space just once.

Normally you'd try something like this

SELECT Id, CaseNumber, Subject
FROM Case
WHERE Id IN (SELECT LinkedEntityId FROM ContentDocumentLink)

But it won't compile, there are some limitations. Experiment, worst case query all cases and use apex to inspect the related list?

SELECT Id, CaseNumber, Subject, (SELECT ContentDocumentId FROM ContentDocumentLinks LIMIT 1)
FROM Case

Case is funny. Attachments linked to emails (if you have Email-to-Case) will show on related list of Files but they won't be physically there. The full link will be Content Document -> ContentDocumentLink -> EmailMessage -> Case. Argh. CombinedAttachment is supposed to help with it, special readonly related list / view / whatever.

One more idea using polymorphic soql, bit bass-ackwards but give it a go:

SELECT ContentDocumentId, TYPEOF LinkedEntity WHEN Case THEN Id, Subject, CaseNumber END
FROM ContentDocumentLink
WHERE LinkedEntityId IN (SELECT Id FROM Case)
eyescream
  • 18,088
  • 2
  • 34
  • 46