Query opportunities with open activities

Question

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

Answer 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