Use Set<Id> in dynamic SOQL IN-clause in method without Database.query()

Question

I know and love that Apex is automatically converting Set to a valid SOQL string when it is used referenced like

Set<Id> ids = ...
String soql = 'SELECT ... WHERE Id IN :ids';

and passed to a Database.query() in the SAME method

Database.query(soql);

But how can I get that when my method does NOT include the actual database query?

Database.query( SoqlStatement.toString() );

I know I could convert it with loops and string manipulation but maybe someone came up with a better solution, e.g. a method where you pass in a Set that are referenced in the soql string and get them bound again ?!

Answer 1

I've just tried some apex to execute the Database.query() dynamically. The logic is to get the soql query from somewhere and put it to the Database.query() in some method that gets the set of id's at the same time (i hope i understand the question right). So i've created a list method that gets a soql as a string where the set name not specifically defined (just a XXX placeholder) and the actual set name. It works perfectly. I could store soql strings in the database in some fields, it does not matter. Yes, i can not bind to the objects field directly, but i can read this field first, put it to the string and then bind it. But for this simple example i will store sets and soqls directly in the class. I can create the final Database.query() parameter dynamically as i wish.

public class MyTestClass{

    public List<sObject> myList {get; set;}
    public Set<Id> set1 = new Set<id>{'001E000000fpkEJ'};
    public Set<Id> set2 = new Set<id>{'006E0000005Ry3S'};

    public final String soql1 = 'Select Name From Account Where Id IN :XXX';
    public final String soql2 = 'Select Name From Opportunity Where Id IN :XXX';

    public MyTestClass(){
        myList = new List<Account>();
    }

    public List<sObject> objects(String setName, String soqlString){
        return Database.query(soqlString.replace('XXX',setName));
    }

    public PageReference soqlExecuter1(){
        myList.clear();
        myList = objects('set1', soql1);
        return null;
    }

    public PageReference soqlExecuter2(){
        myList.clear();
        myList = objects('set2', soql2);
        return null;
    }
}

I can generate a list of different unknown objects types just with one method with unknown soql string and unknown set name. I hope this helps.

With the following simple page i can read unknown objects:

<apex:page controller="MyTestClass">
<apex:form>
    <apex:pageBlock>
        <apex:pageBlockButtons>
            <apex:commandButton action="{!soqlExecuter1}" reRender="myTable" value="Execute1"/>
            <apex:commandButton action="{!soqlExecuter2}" reRender="myTable" value="Execute2"/>
        </apex:pageBlockButtons>

        <apex:pageBlockTable value="{!myList}" var="a" id="myTable">
            <apex:column value="{!a['name']}" />
        </apex:pageBlockTable>
    </apex:pageBlock>
</apex:form>
</apex:page> 

Just results of the code above:

And yes, this is all based on the official and credible documentation , like:

However, unlike inline SOQL, dynamic SOQL can’t use bind variable fields in the query string. (source)

BUT

You can instead resolve the variable field into a string and use the string in your dynamic SOQL query (source)

Well, since this is just a string i will use another official and credible source to modify my string, like String instance methods.

Answer 2

Please only answer when you found a way to do this without a method that manually constructs the IN Clause.

I already found an even shorter, loopless solution, but I thought the auto-replacement done by Database.query() can simulated elsewhere.

public String inClausify(Set<Id> ids) {
    String inClause = String.format( '(\'\'{0}\'\')', 
                         new List<String> { String.join( new List<Id>(ids) , '\',\'') });
    return inClause;
}

Turns out one needs to escape ' twice in the template. Pointed out by http://www.fishofprey.com/2011/03/apex-stringformat-syntax.html

Answer 3

You can actually include variable references in Dynamic SOQL strings, you just need to ensure there is a variable of that name and correct type in scope when your query is executed for example...

public with sharing class DynamicSOQLDemo {

    public static String buildQuery()
    {
        return 'select Id, Name from Account where Id in :accountIds';
    }

    public static void executeQuery()
    {
        Id accountId = '001G000000qdART';
        Set<Id> accountIds = new Set<Id> { accountId };
        List<Account> accounts = Database.query(buildQuery());
        System.assertEquals(accountIds.size(), accounts.size());
    }
}

You obviously need to take a little care with this, as the risk is even greater for runtime issues if the bind variable names change either in the query or in the code ultimately making the Database.query call.

As per the reference in the formal docs also...

You can use simple bind variables in dynamic SOQL query strings. The following is allowed:

String myTestString = 'TestName';
List<sObject> L = Database.query('SELECT Id FROM MyCustomObject__c WHERE Name = :myTestString');

However, unlike inline SOQL, dynamic SOQL can’t use bind variable fields in the query string. The following example isn’t supported and results in a Variable does not exist error:

MyCustomObject__c myVariable = new MyCustomObject__c(field1__c ='TestField');
List<sObject> L = Database.query('SELECT Id FROM MyCustomObject__c WHERE field1__c = :myVariable.field1__c');

Further Update: Following a bit more clarification on the ask here. As regards making this even more dynamic, while the doc snippet above doesn't go into much further detail (e.g. Apex dereferences). I did just try, sorry no joy with this approach... even tried a property get/set....

public with sharing class DynamicSOQLDemo {

    public static String buildQuery()
    {
        return 'select Id, Name from Account where Id in :params.ids';
    }

    public static void executeQuery()
    {
        Id accountId = '001G000000qdART';
        Set<Id> accountIds = new Set<Id> { accountId };
        QueryParams params = new QueryParams();
        params.ids = accountIds;
        List<Account> accounts = Database.query(buildQuery());
        System.assertEquals(accountIds.size(), accounts.size());
    }

    public class QueryParams
    {
        public Set<Id> ids {get; set;}  
    }
}

System.QueryException: Variable does not exist: params.ids

P.S. I assume you probably would rule out making a outbound Apex REST Execute Anonymous callout.... #wherethereisawillthereisaway ;-)