1

I would like to be able to query all opportunities that have open events. So far, I know how to do it with SOQL + Apex, but is there a way to do it just with SOQL?

I'm looking for something like this :

SELECT Id, Name,
  (SELECT Id, Subject FROM Tasks),
  (SELECT Id, Subject FROM Events)
FROM Opportunity
WHERE IsClosed = false 
AND StageName != 'Ganada'
AND StageName != 'Perdida'
AND LastModifiedDate <= LAST_N_DAYS:30
AND Id IN (SELECT WhatId FROM Tasks WHERE isClosed = false)

The problem is that I'm getting:

sObject type 'Tasks' is not supported

0

1 Answer 1

1

Activities are strange in many ways, and I forgot they are not allowed in a Left Inner Join. The way I would get around this is to create a rollup summary via Declarative Lookup Rollup Summaries. Then your query would look something like:

SELECT ... FROM Opportunity
WHERE ...
AND Open_Task_Count__c > 0

You have two different kinds of join on the Task table in your query:

  1. A Left Outer Join
    This sub-query adds a collection of child records to the SELECT clause.

  2. A Left Inner Join
    This sub-query adds a filter to your WHERE clause, restricting which parents are returned.

This first type of join uses the ChildRelationship whose relationship name is Tasks. Because of that, you query FROM Tasks. However in the second join you join the table directly, so you need to query FROM Task.

SELECT Name, (SELECT Subject FROM Tasks)
                                  ^^^^^
                                  Left Outer Join uses Child Relationship Name
FROM Opportunity WHERE Id IN (SELECT WhatId FROM Task)
                                                 ^^^^
                                                 Left Inner Join uses SObject Name
3
  • I didn't know that, thanks! Nonetheless, that isn't allowed either: Entity 'Task' is not supported for semi join inner selects. Aug 2, 2017 at 17:18
  • 2
    @JavierGarcíaManzano Ah, well activities are strange and I guess it's not surprising. Looks like you can't do it in just one query, but this answer should clarify how the syntax works. You might consider using dlrs to create a Lookup Rollup Summary and dump it into an Opportunity field called Open_Task_Count__c.
    – Adrian Larson
    Aug 2, 2017 at 17:20
  • 1
    Aw, pity. Tbh, one part of me kinda knew it wasn't going to be posible. Great answer and thanks! Aug 2, 2017 at 17:24

You must log in to answer this question.

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