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.
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)
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.