[SalesForce] Use Set in dynamic SOQL IN-clause in method without Database.query()

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 ?!

Best Answer

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:

enter image description here enter image description here

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.

Related Topic