SOQL Query - Parent Child Query Related to Specific Object

Question

I'm working with a SOQL query that is supposed to find all Cases related to Consumer Contract that have attachments.

Cases -----> Attachment, related to Consumer Contract.

I'm able to narrow down cases that are related to the Consumer Contract, however, I'm unable to only pull those cases that are related AND have attachments.

My current query looks like:

Select Id, (Select Id, ParentId 
              From Case.Attachments 
             WHERE (NOT Description LIKE 'Redaction Audit%'))
  FROM Case 
 WHERE Case_Consumer_Contract_CC__c != null 
   AND CreatedDate > 2016-02-01T00:00:00.000Z

The attachment object has two fields ID, and ParentID.

Thank you in advance!

Answer 1

Try your SOQL from Attachment instead of doing it from Case

SELECT Id,ParentId From Attachment 
WHERE ParentId IN (
SELECT Id FROM Case
WHERE Case_Consumer_Contract_CC__c != null 
AND CreatedDate > 2016-02-01T00:00:00.000Z
) 
AND (NOT Description LIKE 'Redaction Audit%')
AND Parent.Type = 'Case'

Note:

• ParentId field will have Case objects Id

Update:

• Added AND Parent.Type = 'Case' since Attachment is polymorphic table and free selects with loose conditions might have huge impact on your performance

Answer 2

Realized the only data you need is from Attachment, so @karthikselva has the more succinct answer. But if you need more complex data from Case and would like to save queries, some of the following might be helpful for you.

---

Unfortunately, Attachment does not support the following, but normally you could do:

SELECT Id FROM Case WHERE Id IN (SELECT ParentId FROM Attachment)

Which yields the error:

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

So, semi join inner select is not an option. At this point you have a couple options:

• Use declarative-lookup-rollup-summaries (DLRS) to calculate number of Attachment records per Account.

  • You won't have to write a single Apex Class or Apex Trigger to implement this solution.
  • You get the batch code for free with the same Lookup Rollup Summary, meaning you can backfill this data for all Case records easily.

• Write an aggregate query on Attachment to figure it out on the fly

I highly recommend the first approach, because querying for the aggregates will require you to at least consume one query row from your limit for each Attachment looking up to an Case.

---

If you implement DLRS, you need to:

• Create a number field to write to on Case along the lines of Attachment_Count__c.

• Create a Lookup Rollup Summary with the following values:

  • Parent Object: Case
  • Child Object: Attachment
  • Relationship Field: ParentId
  • Field to Aggregate: Id
  • Aggregate Operation: COUNT
  • Aggregate Result Field: Attachment_Count__c

Then you can just add the following clause to your query: WHERE Attachment_Count__c > 0.

---

If you feel the need to add a query on Attachment, you can do the following:

Set<Id> caseIdsWithAttachment = new Set<Id>();
for (AggregateResult aggregate : [
    SELECT ParentId FROM Attachment WHERE Parent.Type = 'Case' GROUP BY ParentId
]) caseIdsWithAttachment.add((Id)aggregate.get('ParentId'));
List<Case> withAttachment = [SELECT ... FROM Case WHERE Id IN :caseIdsWithAttachment];