ISSUE:
I have a SOQL of that returns a reasonable amount of rows of Campaign based on Contact Ids (i.e. 200 batch of contacts maybe 1000 connected Campaigns) But it also returns an relationship query column of CampaignMembers which I believe is being referred to as an Aggregate query in the error I get during bulk INSERTS/UPDATES:
System.DmlException: Insert failed.
First exception on row 0; first error: CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY,
Quote: execution of AfterInsert
caused by: System.QueryException:
Aggregate query has too many rows for direct assignment, use FOR loop
Its possible that if 1000 campaigns rows come back there could be 50+ CampaignMembers listed in this aggregate column. From what I can tell, whatever the numbers are its too many and causing this error. When in Sandbox or any instance where there is not enough data to have these results everything works as perfectly as expected.
QUESTION:
Do I need to use Batch Apex for this one SOQL? If so any idea how I would do that? (I've never done it before but just read about it on this forum here)
REFERENCE:
Here is my soql statement for reference of what I am returning
SELECT Id, StartDate, EndDate,
(SELECT Id FROM CampaignMembers
WHERE ContactId IN :contact_list AND
CampaignId IN :campaignIdList)
FROM Campaign
WHERE Id IN :campaignIdList AND
StartDate != null AND
EndDate != null AND
RecordTypeId IN (SELECT Id FROM RecordType
WHERE Name = \'Personal Touch\')
ORDER BY StartDate ASC LIMIT 50000
As you can see I've tried to add WHERE statements to limit the returned values as much as possible, so even the CampaignMembers in the aggregate column are reduced to only the Campaigns that I'm expecting.
Best Answer
I assume that you already considered to use a for loop without assigning the query result to List. (The "For loop list batch size" will apply.)
However, let's improve the overall design:
The problem in detail
In apex every
FROM
statement counts as a separate SOQL query. Your query contains 3 FROM statements.Almost every salesforce limit applies only for one execution context. This means you can create a bunch of execution contexts and you will workaround limits.
The batchable interface works as follows:
And:
Remember: Every call of execute() creates a new execution context. Therefore you are able to query more records. However, the following solution reduces the number of query per execution context to 1. And lowers the number of queried records.
Query Enhancements
1. Remove unnecessary queries:
Replace the following line
with
2. Split your query in 2 different queries
This will allow you to use Batch functionality.
In a first step you have to query the campaigns:
In a second query all CampaignMembers
3. Make sure that the functionality works fine (in your sandbox)
Batchify your program
Create a new class which implements the interface Database.Batchable make sure that you are using
Database.QueryLocator
. Move your campaign query to the batchable class and call your "functionality class" in the execute statement.Your batchable class should look similar to this:
And your functionality class: