Nested query doesn't work

Question

EDIT: I am using simple-salesforce with the REST API to make requests.

I'm trying to get for each Case all the EmailMessages, and for each EmailMessage, all its ContentDocumentLinks (from which I can get the ContentDocument).

This query fails with "Malformed Query":

SELECT
    Id,
    (
    SELECT
        Id,
        (
        SELECT
            Id
        FROM
            ContentDocumentLinks
        )
    FROM
        EmailMessages
    )
FROM
    Case
LIMIT 1

However these two queries work perfectly:

SELECT
    Id,
    (
    SELECT
        Id
    FROM
        ContentDocumentLinks
    )
FROM
    EmailMessage
LIMIT 1

SELECT
    Id,
    (
    SELECT
        Id
    FROM
        EmailMessages
    )
FROM
    Case
LIMIT 1

What did I miss? Is there a way to achieve what I wanted?

Answer 1

edit:

So the question ended up being about simple salesforce (python) and thus running this query through the REST API.

Having multiple levels of subqueries was introduced in Summer '23 (api v58.0), and can be taken advantage of if you're performing the query through the REST API.

You just need to make sure to specify an API version of at least 58 when using simple salesforce to login.

e.g.

sf = Salesforce(
    username='[email protected]', 
    password='password', 
    security_token='token', 
    version='58.0')

or

session_id, instance = SalesforceLogin(
    username='[email protected]',
    password='password',
    security_token='token',
    sf_version='58.0')

Alternatively, keeping simple-salesforce up to date with pip install simple-salesforce --upgrade should do the trick. The DEFAULT_API_VERSION constant is updated every so often, and has been set to 59.0 since v1.12.6 (2024-02-26). The latest version on pypi is currently 1.12.5 though.

Original answer:

In Apex, we're only allowed to query down one level of an object hierarchy. I.e. you can't have a subquery in a subquery like you've tried to do.

You either need to break it up into two separate queries, or change the FROM object to be on EmailMessage.

If you already have the Case Id, then it'd be something like

SELECT
    /* While we can only query down one level, we can query "up" */
    /* the hierarchy (from child -> parent) up to 5 levels */
    /* E.g. Parent.Account.Parent.Owner.Manager.Name */
    Parent.Id,
    Id, TextBody, To,
    (SELECT Id FROM ContentDocumentLinks)
FROM
    EmailMessage
WHERE
    ParentId = :myCaseId