SOQL relationships and pagination
Query records with SOQL, traverse parent relationships in one request and page through large result sets.
Overview
SOQL is how you find Salesforce records by their business attributes. A query is a GET on the /query/ path with the SOQL text in the q parameter - always URL-encode it with quote, never paste it into the path by hand.
# -*- coding: utf-8 -*-
# stdlib
from urllib.parse import quote
# Zato
from zato.server.service import Service
class FindCampaigns(Service):
name = 'crm.find-campaigns'
def handle(self):
conn = self.salesforce['My Salesforce Connection']
# The query to run ..
query = "SELECT Id, Name, Segment__c FROM Campaign WHERE IsActive = true"
# .. run it now ..
response = conn.get('/query/?q=' + quote(query))
# .. and log how many records matched.
total_size = response['totalSize']
self.logger.info('Records matched: %s', total_size)
The response carries the records plus the metadata that drives pagination:
{
"totalSize": 3,
"done": true,
"records": [
{"attributes": {"type": "Campaign"}, "Id": "701...", "Name": "...", "Segment__c": "..."}
]
}
Traversing relationships
One query can pull fields of related parent objects - name the parent in the select list with a dot. Standard relationships use the object name, custom relationships use the __r suffix:
columns = ', '.join([
# Fields of the opportunity itself
'Id', 'Name', 'StageName', 'Amount', 'CloseDate',
# Fields of the standard parent account
'Account.Name', 'Account.BillingCountry',
# Fields reached through a custom relationship
'Partner_Account__r.Name', 'Partner_Account__r.Partner_Code__c',
])
query = f'SELECT {columns} FROM Opportunity ' + \
"WHERE Channel_Or_Direct__c = 'Channel' " + \
'ORDER BY Name ASC'
This matters more than it looks - each relationship traversed in the select list is one lookup you do not have to make as a separate API call. A sync that reads a thousand opportunities and their accounts is one query with traversal, or one thousand and one requests without it, and your org's API request allowance notices the difference.
In the response, parent fields arrive as nested objects:
{
"Name": "ACME renewal",
"Account": {"Name": "ACME Corp", "BillingCountry": "Germany"},
"Partner_Account__r": {"Name": "Reseller Ltd", "Partner_Code__c": "RSL-001"}
}
Note that a record with an empty relationship carries null for the whole nested object, so read parent fields only after checking the parent is there.
Pagination
Salesforce returns at most 2,000 records per response. When more remain, done is false and nextRecordsUrl points to the next page - pass that value straight to conn.get and loop until done:
# -*- coding: utf-8 -*-
# stdlib
from urllib.parse import quote
# Zato
from zato.server.service import Service
class CollectAllOpportunities(Service):
name = 'crm.collect-all-opportunities'
def handle(self):
conn = self.salesforce['My Salesforce Connection']
query = 'SELECT Id, Name, Amount FROM Opportunity ORDER BY Name ASC'
response = conn.get('/query/?q=' + quote(query))
page_records = response['records']
records = list(page_records)
# Follow the pagination trail until Salesforce reports the result set is complete ..
while not response['done']:
next_records_url = response['nextRecordsUrl']
response = conn.get(next_records_url)
page_records = response['records']
records.extend(page_records)
# .. now every page has arrived.
record_count = len(records)
total_size = response['totalSize']
self.logger.info('Records collected: %s of %s', record_count, total_size)
Two things to know about the loop:
- nextRecordsUrl values already carry the full API prefix, e.g. /services/data/v54.0/query/01gRO0000016PIAYA2-2000, and conn.get recognizes that and uses them as they are.
- Query locators expire after a while, so consume all pages within one run rather than storing a locator for later.
For result sets so large that even paging through them is impractical, filter harder - a WHERE clause on LastModifiedDate that only picks up what changed since the previous run is the difference between a sync that reads hundreds of records and one that reads hundreds of thousands. The CRM sync scenario shows this end to end.