2

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!

3
  • 1
    you need to query two times.. first query Attachments then get ParentId and then query on Case – Ratan Paul Mar 1 '16 at 15:43
  • I am able to execute your query, what is the exact problem ? – karthikselva Mar 1 '16 at 15:46
  • @karthikselva The query runs okay, but I want to pull just cases with attachments, rather than all cases related to Consumer Contract. – Mowrite Mar 1 '16 at 15:47
2

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
3
  • 2
    Rats, I had a great explanation...that was too complicated. Didn't realize the only data being selected was the child list itself. Nice and succinct. No need to talk about semi join inner selects or dlrs. – Adrian Larson Mar 1 '16 at 16:08
  • thank you :) but still curious on why I am not able to do semi joins on Attachment object, I am sure your explanation will enlighten me – karthikselva Mar 1 '16 at 16:30
  • 1
    Added it since it was already mostly typed up. No insight into why they're not allowed, other than just not being supported. – Adrian Larson Mar 1 '16 at 16:34
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];
1
  • 2
    my rough guess is because of its polymorphic nature. I have never used DLRS and its good to know one alternative approach. Thank you :) – karthikselva Mar 1 '16 at 16:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.