30

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

8
  • 1
    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. Nov 4, 2013 at 22:03
  • 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. :-) Nov 6, 2013 at 1:26
  • @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<Id> do I don't have to magically create it in the second method. I will think about it... Nov 6, 2013 at 9:06
  • 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? :-) Nov 8, 2013 at 23:02
  • @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. Nov 9, 2013 at 2:06

3 Answers 3

12

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.

23

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

3
  • Awesome answer !I am deleting mine after this ! Nov 4, 2013 at 10:31
  • It keeps bugging me.. How did you find the 3 parameter version of string.join? The docs and developer console mention only 2 params
    – user682217
    Sep 27, 2014 at 21:12
  • @user682217 there is no 3 param version of that method! It's the regular 2 param version which is joining the elements with a comma and sourounding single quotes which need to be escaped. Sep 29, 2014 at 7:09
14
+100

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 ;-)

2
  • 1
    It can't be a "function" call or a deference at all. For example, you can bind to a set variable, but not a map's keyset (because you have to call a function). You can bind to a list, but not an element in the list. You can bind to a local string variable, or a class visible variable, but not a static variable (because you have to dereference the class). I think you get the idea.
    – sfdcfox
    Nov 11, 2013 at 19:09
  • 2
    Looks like this answer has received a down vote, part of the course of course i know. :-) Just curious if anyone wants to elaborate as to what aspect of it is incorrect or needs improvement? No worries if not. :-) Nov 14, 2013 at 14:49

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .