Use Set<Id> 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 ?!

    I've done some pretty extensive research in this area looking for an answer to just this question and was left without any satisfactory answer. I'll slap a bounty on this once I'm able.

    Curious if my answer is the answer for you here? Or did I miss something? Always up for a challenge if so, just wondering if there is something I've missed in reading the question. :-)

    @AndrewFawcett: your answer shows that splitting a query so that the SOQL is generated in a seperate method would indeed work. But I don't see how I really can pass - in a generic way - the Set do I don't have to magically create it in the second method. I will think about it...

    So you want to pass out the Id list and the SOQL method from the method and then have the caller combine the two together in a Database.query? Still sorta wondering if I am being a bit thick here, especially since I see +100 bounty on it? :-)

    @AndrewFawcett the goal could be reprhased as a way to pass and unknown until runtime number of objects into a SOQL query using bind syntax. Bind syntax does a whole bunch of things, from escaping to conversion of collections and prevents huge query strings that might hit character limits.

    Yep totally agree, still wondering why my answer does not cover that? Am I still missing something?

    @AndrewFawcett your answer covers most use cases, but doesn't support being abstracted; since locally scoped variables of the appropriate types need to be declared at compile time, rather than runtime.

    Got it, i see, i've updated the answer a bit, but i think the main restriction is when using dynamic soql it only supports native types for bindings.

  • Sergej Utko

    Sergej Utko Correct answer

    8 years ago

    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.

License under CC-BY-SA with attribution


Content dated before 7/24/2021 11:53 AM