Reference Set<Id> in the WHERE clause of a Dynamic SOQL in Batch class

Question

I have a Batch class and I am trying to reference a Set in the WHERE clause of a dynamic SOQL query but I have an error: System.QueryException: expecting a colon, found '{kA09E0000005mCySAI}'

when using the syntax:

query = 'Select Id, KnowledgeArticleId, Known_Error_Status__c FROM Knowledge__kav WHERE PublishStatus=\'Online\'' + 
            ' AND KnowledgeArticleId IN \''+kesIds+'\' LIMIT 1';  

I have also tried to use the syntax below but another error appears: Variable kesIds does not exist:

query = 'Select Id, KnowledgeArticleId, Known_Error_Status__c FROM Knowledge__kav WHERE PublishStatus=\'Online\'' + 
                'AND KnowledgeArticleId IN :kesIds';

How can I fix the errors and reference my Set kesIds in the Dynamic SOQL? Thanks

here is a snippet of my batch:

global class BatchArticleEmail implements Schedulable, Database.Batchable<SObject> {

global string query;

// To schedule batch 
global void execute(SchedulableContext sc) {
    Id batchProcessId = Database.executeBatch(this);
}

// Constructor to query articles
global BatchArticleEmail() {
    
    Set<Id> kesIds = new Set<Id>();
   
    List<Known_Error_Subscription__c> kesList = [SELECT KnowledgeKA__c FROM Known_Error_Subscription__c];
    for(Known_Error_Subscription__c kes : kesList) {
        kesIds.add(kes.KnowledgeKA__c);
    }
    
    query = 'Select Id, KnowledgeArticleId, Known_Error_Status__c FROM Knowledge__kav WHERE PublishStatus=\'Online\'' + 
            ' AND KnowledgeArticleId IN \''+kesIds+'\' LIMIT 1';     
    System.debug('query BatchArticleEmail ' +query);
}

Answer 1

Set<ID> accId = new Set<ID>();
accId.add('0016F00002QHh3E');
String query = 'Select id from Account Where ID IN :accId';
for(Account acc : Database.query(query)){
  System.debug('acc=>'+acc);
}

Its working for me You only need to set keyids variable global variable

Answer 2

Bind variables e.g. :kesIds are a good way to go because they leave the type conversion formatting up to the framework and in the process also eliminate the SOQL injection risk. And make your code simpler and much easier to read.

But the variable must be in scope - visible to the code - at the point that Database.query executes as illustrated in Rijwandeltax's answer. So it makes sense to build the query string immediately before the Database.query call and to declare the bind variables immediately before too. Otherwise anyone working in the future may make a braking change that the compiler doesn't detect. (This is an example of keeping interdependent things close together.)

But further than that, dynamic SOQL where the query is constructed as a string means you entirely miss out on the compiler's ability to check the query for you. Dynamic SOQL is only needed where the structure of the query needs to change. In your case it is just the values that are changing so:

private void yourMethod() {

    String status = 'Online';
    Set<Id> kesIds = ...;

    Knowledge__kav[] recordds = [
        Select Id, KnowledgeArticleId, Known_Error_Status__c
        FROM Knowledge__kav
        WHERE PublishStatus = :status
        AND KnowledgeArticleId IN :kesIds
    ];

    ...
}