# SOQL relationships and pagination

Query records with SOQL, traverse parent relationships in one request and page through large result sets.

## Overview {#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.

```python
# -*- 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 contains the records plus the metadata that drives pagination:

```json
{
  "totalSize": 3,
  "done": true,
  "records": [
    {"attributes": {"type": "Campaign"}, "Id": "701...", "Name": "...", "Segment__c": "..."}
  ]
}
```

## Traversing relationships {#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:

```python
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 the difference counts directly against your org's [API request allowance](https://zato.io/docs/dev/salesforce/error-handling-and-api-limits.html).

In the response, parent fields arrive as nested objects:

```json
{
  "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 has null in place of the whole nested object, so read parent fields only after checking the parent is there.

## Pagination {#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 unchanged to conn.get and loop until done:

```python
# -*- 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 include 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 selects only 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](https://zato.io/docs/dev/salesforce/crm-sync-scenario.html) shows this end to end.

## Learn more {#learn-more}

- [Salesforce integration overview](https://zato.io/integrations/salesforce/) - Start here to see how teams build Salesforce integrations
- [The REST API from Python](https://zato.io/docs/dev/salesforce/rest-api-python.html) - Connected apps, credentials, connections and the self.salesforce API
- [Create and query records](https://zato.io/docs/dev/salesforce/create-and-query-records.html) - A REST channel that creates campaigns and reads them back
- [Upserts and external IDs](https://zato.io/docs/dev/salesforce/upserts-and-external-ids.html) - Idempotent create-or-update, keyed by your own identifiers
- [Updates and deletes](https://zato.io/docs/dev/salesforce/updates-and-deletes.html) - Diff-driven partial updates and deleting records by ID
- [Error handling and API limits](https://zato.io/docs/dev/salesforce/error-handling-and-api-limits.html) - Error arrays, failure policies and the daily request allowance
- [A CRM sync, end to end](https://zato.io/docs/dev/salesforce/crm-sync-scenario.html) - A scheduled one-way sync with correlation keys and drift detection