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
Best Answer
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:You have two different kinds of join on the
Task
table in your query:A
Left Outer Join
This sub-query adds a collection of child records to the
SELECT
clause.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 isTasks
. Because of that, you queryFROM Tasks
. However in the second join you join the table directly, so you need to queryFROM Task
.