Apex Batch Unexpected token '{' in dynamic SOQL

Question

I have some apex code thats supposed to accept a String query variable. Now in my trigger when im assigning this variable I get the Unexpected toke error below:

16:59:43:145 USER_DEBUG [26]|DEBUG|query = SELECT Id, StageName, AccountId, Thank_You_email_sent__c FROM Opportunity WHERE AccountId != null AND (StageName = 'Posted' AND Thank_You_email_sent__c = false) AND Id IN : {0068E000003VS7pQAG}

Which is weird because I was under the impression this worked. Im sure im missing something silly. Can someone help. Below is my trigger as well as the batch apex class

Trigger:

trigger DonationUpdate on Opportunity (after update,after insert) {
    //Send emails to donors of the donation account
    System.debug('Total Number of SOQL Queries allowed in this Apex code context: ' +  Limits.getLimitQueries());
    System.debug('Total Number of records that can be queried  in this Apex code context: ' +  Limits.getLimitDmlRows());
    System.debug('Total Number of DML statements allowed in this Apex code context: ' +  Limits.getLimitDmlStatements() );
    System.debug('Total Number of CPU usage time (in ms) allowed in this Apex code context: ' +  Limits.getLimitCpuTime());
    System.debug('');

    Map<Id, Opportunity> donations = new Map<Id, Opportunity>(
        [
            SELECT Id, StageName, AccountId, Thank_You_email_sent__c 
            FROM Opportunity
            WHERE AccountId != null AND 
                (StageName = 'Posted' AND Thank_You_email_sent__c = false) AND
                Id IN : Trigger.newMap.keySet()
        ]
    );
    if (!donations.isEmpty()) {
        //Execute batch
        Set<Id> donationIds = donations.keySet();
        String query = 'SELECT Id, StageName, AccountId, Thank_You_email_sent__c ' +
            'FROM Opportunity ' +
            'WHERE AccountId != null AND ' + 
                '(StageName = \'Posted\' AND Thank_You_email_sent__c = false) AND ' +
                'Id IN : ' + donationIds;
        System.debug('query = ' + query);
        DonationTriggerHandler.SendThankYouEmail(query);
    }

    System.debug('');
    System.debug('1. Number of Queries used in this Apex code so far: ' + Limits.getQueries());
    System.debug('2. Number of rows queried in this Apex code so far: ' + Limits.getDmlRows());
    System.debug('3. Number of DML statements used so far: ' +  Limits.getDmlStatements());    
    System.debug('4. Amount of CPU time (in ms) used so far: ' + Limits.getCpuTime());    

}

Batch Apex:

global class DonationSendThankYouEmail implements Database.Batchable<SObject>{
    global final String query;
    global final EmailTemplate emailTemplate = [
        SELECT Id, Name 
        FROM EmailTemplate 
        WHERE DeveloperName = 'Thank_you_HTML_Contact'
    ];
    //Get all organisation type account records
    global final Map<Id, Account> organisations = new Map<Id, Account>(
        [
            SELECT Id, Name 
            FROM Account 
            WHERE RecordTypeId IN : 
            [
                SELECT Id, Name FROM RecordType 
                WHERE SobjectType = 'Account' AND DeveloperName = 'Organization' LIMIT 1
            ]          
        ]
    ); 
    //Get all person account type account records
    global final Map<Id, Account> persons = new Map<Id, Account>(
        [
            SELECT Id, Name, PersonContactId
            FROM Account 
            WHERE IsPersonAccount = true         
        ]
    );
    //Get all person account contact records
    global final Map<Id, Contact> contacts = new Map<Id, Contact>(
        [
            SELECT Id, Email 
            FROM Contact
            WHERE AccountId IN : persons.keySet()
        ]
    );

    global DonationSendThankYouEmail(String query) {
        this.query = query;
    }

    global Database.QueryLocator start(Database.BatchableContext BC) {
        return Database.getQueryLocator(query);
    }

    global void execute(Database.BatchableContext BC, List<SObject> scope) {
        //Send Emails
        Messaging.SingleEmailMessage[] emailList = new Messaging.SingleEmailMessage[]{}; 
        System.debug('scope = ' + scope);
        Set<Account> donationOrganisations = new Set<Account>();
        for (Opportunity donation : (List<Opportunity>)scope) {
            Account organisation = organisations.get(donation.AccountId);
            System.debug('organisation = ' + organisation);
            if (organisation != null) {
                donationOrganisations.add(organisation);
            }
        }
        System.debug('donationOrganisations = ' + donationOrganisations);
        organisations.clear();
        for (Account organisation : donationOrganisations) {
            System.debug('organisation = ' + organisation);
            organisations.put(organisation.Id, organisation);
        }
        System.debug('donationOrganisations = ' + organisations);
        //Get donation organisation affiliated accounts
        Map<Id, Affiliated_Account__c> donationOrganisationaffiliatedAccounts = new Map<Id, Affiliated_Account__c>(
            [
                SELECT Id, Person_Account__c, Organisation_Account__c
                FROM Affiliated_Account__c 
                WHERE Organisation_Account__c IN : organisations.keySet()          
            ]
        );
        System.debug('donationOrganisationaffiliatedAccounts = ' + donationOrganisationaffiliatedAccounts);
        //Send emails
        for (Opportunity donation : (List<Opportunity>)scope) {
            Account organisation = organisations.get(donation.AccountId); 
            for (Affiliated_Account__c affiliate : donationOrganisationaffiliatedAccounts.values()) {
                if (affiliate.Organisation_Account__c == organisation.Id) {
                    Account affiliateAccount = persons.get(affiliate.Person_Account__c);
                    Contact affiliatedContact = contacts.get(affiliateAccount.PersonContactId);
                    Messaging.SingleEmailMessage mail = new Messaging.SingleEmailMessage();
                    mail.setTargetObjectId(affiliatedContact.Id);
                    mail.setWhatId(donation.Id);
                    mail.setSaveAsActivity(true);
                    mail.setTemplateId(emailTemplate.Id);                                
                    mail.setToAddresses(new String[]{affiliatedContact.Email});
                    emailList.add(mail);               
                }
            }           
        }
        if(!emailList.isEmpty()){
            System.debug('DonationEmailList: '+ emailList);
            try{                
                //Send Emails
                List<Messaging.SendEmailResult> msg = Messaging.sendEmail(emailList);
                for (Opportunity donation : (List<Opportunity>)scope) {
                    donation.Thank_You_email_sent__c = true;
                }
                update scope;
            } catch(Exception e){
                System.debug('Exception caught:::'+e.getMessage());
            }
        }             
    }

    global void finish(Database.BatchableContext BC) {
        AsyncApexJob a = [
            SELECT Id, Status, NumberOfErrors, JobItemsProcessed, TotalJobItems, CreatedBy.Email
            FROM AsyncApexJob
            WHERE Id =: BC.getJobId()
        ];

        //Send an email to the Apex job's submitter notifying job completion
        Messaging.SingleEmailMessage mail = new Messaging.SingleEmailMessage();
        String[] toAddresses = new String[] {a.CreatedBy.Email};            
            mail.setToAddresses(toAddresses);
            mail.setSubject('Record Clean Up Status: ' + a.Status);
        mail.setPlainTextBody(
            'The batch Apex job processed ' + a.TotalJobItems +
             ' batches with '+ a.NumberOfErrors + ' failures.'
        );
        Messaging.sendEmail(new Messaging.SingleEmailMessage[] { mail });
    }

}

I believe the issue is with this section of code:

global Database.QueryLocator start(Database.BatchableContext BC) {
        return Database.getQueryLocator(query);
    }

Answer 1

For a problem like this, passing the ID values in a collection to the Batchable and using the : binding mechanism is cleaner than trying to build all the values into a query string. Also, although the Batchable documentation example uses a string for the the query in the start method, it usually makes sense to use normal static SOQL and so benefit from compile time checking.

Those changes together would result in:

public class XyzBatchable implements Database.Batchable<SObject> {

    private Set<Id> opportunityIds;

    public MyBatchable(Set<Id> opportunityIds) {
        this.opportunityIds = opportunityIds;
    }

    public Database.QueryLocator start(Database.BatchableContext bc) {
        return Database.getQueryLocator([
                SELECT Id, StageName, AccountId, Thank_You_email_sent__c 
                FROM Opportunity
                WHERE AccountId != null
                AND StageName = 'Posted'
                AND Thank_You_email_sent__c = false
                AND Id IN :opportunityIds
                ]);
    }

    ...
}

Answer 2

A few issues:

1. The IN syntax uses parentheses (()), not curly braces ({}).
2. Merge syntax (:) is only for context values, it looks like you are trying to use a literal.
3. You also need to wrap literal values in single quotes ('').

Valid filters:

WHERE Id IN ('0068E000003VS7pQAG')
WHERE Id IN (:someContextId)

Answer 3

The : in your Select statement is expecting a variable not a value. Since you are dynamically building your query with the value the : token needs to be removed. As well as replacing the {}'s with '()'s.