Passing a List in SOQL Query

Question

I'm trying to pass a list of 1000 account IDs and retrieve their corresponding count of Contacts and write the results to a CSV file. I'm using the Anonymous Execute window to run the code below as I need to get this data from multiple Orgs without creating custom Apex classes.

List<Id> accountIds = new List<Id>();
        
// Add the hardcoded account IDs to the list
//Will be adding about 1000 accounts here
accountIds.add('00xxxxxxxxxxxxxxxxxxxxxx');

// Define a list to hold the queried data
List<Contact> contactList = new List<Contact>();

// Define a string to hold the CSV file content
String csvContent = 'Account ID,Number of Contacts,First Name,Last Name,Email\n';

// Query the data and assign it to a list of AggregateResult objects
List<AggregateResult> results = [SELECT COUNT(Id) numContacts, AccountId, MIN(FirstName) firstName, MIN(LastName) lastName, MIN(Email) email FROM Contact WHERE AccountId IN :accountIds GROUP BY AccountId];

// Loop through the results and create a new Contact object for each one
for (AggregateResult result : results) {
    // Extract the values from the AggregateResult object and use them to create a new Contact object
    Contact c = new Contact();
    c.AccountId = (Id)result.get('AccountId');
    c.FirstName = (String)result.get('firstName');
    c.LastName = (String)result.get('lastName');
    c.Email = (String)result.get('email');
    contactList.add(c);
    
    // Add the Account ID, number of contacts, and contact information to the CSV content
    csvContent += c.AccountId + ',' + result.get('numContacts') + ',' + c.FirstName + ',' + c.LastName + ',' + c.Email + '\n';
}

// Write the CSV content to a file
String fileName = 'AccountContacts.csv';
Blob csvBlob = Blob.valueOf(csvContent);
ContentVersion cv = new ContentVersion();
cv.Title = fileName;
cv.PathOnClient = fileName;
cv.VersionData = csvBlob;
insert cv;

I'm getting an error as below

Line: undefined, Column: undefined Response to EXEC was : Bad Message 414 reason: URI Too Long . HTTP CODE[414]

Is there an efficient way to do this? I've been trying to pass 1000 account IDs at a time to retrieve their corresponding Contact count, but nothing seems to be working. I could potentially generate a report that provides this information, but thought I'd check if there is a programmatic way to do it.

Any inputs would be highly appreciated!

Answer 1

Anonymous apex works by sending your code as a REST request to a resource (i.e. a URL) that Salesforce provides, so it is subject to the URI length limit that I worked out back in 2017. That limit is 16,384 characters.

With 1000 Ids (which are 15 or 18 characters long), that wouldn't give you much room to do anything else in your anonymous apex (or potentially overrun the limit completely). 15k characters for the Ids alone + 2 single quotes to enclose each Id + 999 commas to separate them = 17,999 characters right there.
And you also need to consider the characters used in the URL itself, and there's a handful more used to declare the list.

You could work around this by storing this list of Ids in a "static resource", or by having it otherwise stored within Salesforce (Custom Metadata Type records, a static list in some other Apex class, etc...) so that you aren't sending all of those Ids via the URL, but you may run into length issues with the query itself.

The Salesforce Developer Limits and Allocations Quick Reference tells us that:

• SOQL queries can be a maximum of 100k characters (probably not an issue)
• A single "string" in the WHERE clause can be a maximum of 4k characters (potentially an issue, this depends on if Salesforce treats IN :someList as a single string, or if each Id becomes its own "string". I suspect each Id becomes its own string)

In the end, when you find yourself hardcoding so many Ids, you probably want to be querying by something other than the Ids. Some simple ideas to get you started down that path are:

• Define a special checkbox that you only check for these 1000 records, change your query to filter for Special_Checkbox__c = true
• Create a special lookup field to another account, create a special account, then relate your 1000 accounts to this special account. Then change your query to filter for Special_Lookup__c = '<special lookup account id here>'

Both of those would drastically reduce the number of characters in your anonymous apex. The second one (the special lookup field) may stand a better chance of keeping your query "selective" if/when you have over 200k Account records (which is generally when you start having to worry more about queries being "selective" enough).