SOQL nested query on object related to parent?

Question

I have a query on CampaignMember and I would like to get some Tasks for the Contact or Account that is associated with the CampaignMember record. I'm guessing I can't do that since the Tasks relationship is between Task and Contact, not with CampaignMember. Unfortunately I can't do this with Apex as I am producing a Conga Composer document and am limited to SOQL inside Conga Queries.

When I make the Contact the root of the query I can do this (obviously, since there is a Tasks relationship between the Contact and Task) but then I can't pull in the Tasks for the Accounts, or any fields really since the CampaignMember records with Accounts are excluded.

SELECT 
    Id, 
    FirstName,
    LastName, 
    (SELECT 
        Id,  
        Account.Name 
    FROM CampaignMembers
    WHERE CampaignId = '1234567890ABCDEFG'),
    (SELECT 
        Id,
        Who.Name
    FROM 
        Tasks
    WHERE Status = 'Not Started'
)
FROM Contact
WHERE Id IN (
        SELECT ContactId 
        FROM CampaignMember
        WHERE CampaignId = '1234567890ABCDEFG'
    )

What would be cool is if I could do something like this (I know I can't):

SELECT 
    Id, 
    ContactId,
    Contact.FirstName, 
    Contact.LastName, 
    Account.Name,
    (
        SELECT 
            Id,
            Who.Name
        FROM 
            Contacts.Tasks
        WHERE Status = 'Not Started'            
    ),
    (
        SELECT 
            Id,
            Who.Name
        FROM 
            Accounts.Tasks
        WHERE Status = 'Not Started'            
    )
FROM CampaignMember
WHERE CampaignId = '1234567890ABCDEFG'

but clearly I can't since there is no Task relationship to Account. Are there any ways that I can query objects that are related to a record that is related to the root object of my query?

I'm required to build a document that handles Accounts and Contacts together, hence why I went the Campaign route as I can add both objects to a Campaign.

Answer 1

Here's the outline of a solution that I used many years ago when faced with a similar issue

• Make your delivery document a spreadsheet rather than a document
• Add three tabs to the spreadsheet: Summary, Query1, Query2 - you may need additional helper tabs
• On the Query1 tab, bind this to the first Conga query
• On the Query2 tab, bind this to the second Conga query
• Make the Summary tab the default tab when the spreadsheet is opened by the user
• Use formulas on the Summary tab to join the two other tables in tabs Query1, Query2 to get the result intended. You may need a helper tab for pivot tables to assist

Basically, you are using client-side logic that can execute when the spreadsheet is opened by the user to join the data and present in a form suitable for the end user.

The practical details of the spreadsheet formulas can be addressed with assistance from the SuperUser site and other Excel-oriented sites. I used Microsoft's add-in PowerBI to make this task easier.