API: Query all Contacts for multiple Accounts

Question

With a Perl script and one API query I read about 3000 accounts (looping through results with nextUrl). SOQL query string is like SELECT ID, Name FROM Account WHERE ...

What is the best way to get a list of all related contacts? My First idea was to get a string with all Account Ids and use it in query: SELECT Id, EMail FROM Contact WHERE AccountId IN ( $AccountIdString )

But then the resulting query string will be too long (max. 4000 characters in WHERE). So If have to use multiple queries with maybe 500 AccountIds.

Is there a better way which works with only one query?

Answer 1

There are 2 possible ways:

1. Using relationship queries

[Select Id, Name, (Select Id, Email from Contacts) from Account where ...]

You can access the related contacts for each account record using accountRecord.Contacts

https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_relationships_query_using.htm

2. Using cross object references in your where clause

[Select Id, Email from Contact where Account.Field1=Value1 and Account.Field2=Value2];

Answer 2

Use a sub-query:

SELECT Id, ..., (SELECT Id, Name, ... FROM Contacts) FROM Account WHERE ...

Note that for each account, a separate cursor (nextUrl) may also be generated for the contacts if there's more than 2,000 contacts on a single account (less, if a lot of fields are selected).