0

I have arrived to the following SOQL:

 select 
 Id, 
 OwnerId, 
 (SELECT id, ParentId FROM EmailMessages 
 where toLabel(Status) = 'New' and Incoming = true) 
 from Case

This query retrieves the Case ids, owners and EmailMessages related to them, due to the subselect having a where clause some records are null and that's OK, but it still retrieves ALL the cases. So, I wanted to know if there was a way to do any of the following:

  • Limit the results only to cases with EmailMessages in that condition
  • Group by OwnerId, counting the EmailMessages with that condition
  • Use a semi-join with EmailMessages

Any of this would help me to limit the results and execution time significantly.

Thanks in advance!

Improve this question

1 Answer 1

Reset to default
6

Try this:

SELECT 
    Id, 
    OwnerId, 
    (SELECT id, ParentId, FromName FROM EmailMessages 
WHERE toLabel(Status) = 'New' 
AND Incoming = true) 
FROM Case
WHERE Id IN (SELECT 
               ParentId 
               FROM EmailMessage
               WHERE toLabel(Status) = 'New' 
               AND Incoming = true)

Since that query has nested info, it's not possible to group results o aggregate them. You'll have to iterate in order to do calculations

Improve this answer
4
  • This is the right answer but the developer console shows "no response from the server", so I removed the subselect from the results since I only needed the Case ID and Owner and ran OK, thanks!
    – FuuRe
    Commented Jun 2, 2016 at 15:51
  • And i tried your approach with the semi-join before asking here and failed miserably every time, so I assumed some kind of special relation between Cases and Emails due to them both being standard objects and EmailMessages being inaccessible from the Salesforce configuration
    – FuuRe
    Commented Jun 2, 2016 at 15:54
  • 1
    On the semi join you have to use the object name (EmailMessage) instead of the relationship name (EmailMessageS). Maybe that's where the problem is?
    – Fernando Gavinho
    Commented Jun 2, 2016 at 16:12
  • That was it! At first I thought "What was the difference wit the things I tried?" and that was it, the singular on the relationship! you should remark it in your answer
    – FuuRe
    Commented Jun 2, 2016 at 19:34

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .