[SalesForce] Can a SOQL query selecting all columns be generated at runtime

It seems like SOQL does not have an equivalent to SQL's "SELECT * FROM …"

(If SOQL has this syntax, show me and I will mark that as the answer.)

To get around this limitation, I have been using a modified version of the code found here to generate String based SOQL queries. That code works well, but String based queries require that you convert objects into the correct format.

(here is an example of converting a DateTime object to the correct String representation).

Apex's lack of .toString() methods makes the conversion process unduly difficult, and String.valueOf(obj) rarely provides valid SOQL values.

I would like to create a query using the [SELECT Id, … FROM … ] syntax.

Is there a way to dynamically generate a query using that syntax?

Maybe something like:

String columns = 'Id, Name, AnotherColumn';
List<...> records = [SELECT columns FROM ...];

Best Answer

Absolutely there is. Use the following syntax:

// write your query string exactly as in a SOQL statement minus the square-brackets
string queryString = 'SELECT id,Name,OpportunityId FROM Account WHERE OpportunityId!=null LIMIT 5';

// then use database.query() method.  
// Note its return value is a list<sObject> though you can cast it appropriately

list<sObject> sAccountList=database.query(queryString);
list<Account> accountList=(list<Account>)database.query(queryString);

You can also reference instance variables, but not any variable of an instance

set<id> myIdSet;

string queryString = 'SELECT id,Name FROM Account WHERE id IN:myIdSet';
list<Account> accountList = (list<Account>)database.query(queryString);

// XXXXXXXX   The following WONT work    X_X

map<id,Account> accountMap = new map<id,Account>(accountList);

string anotherQueryString = 'SELECT id,Name FROM Account WHERE id IN:accountMap.keyset()';  // X_X WILL NOT WORK 

list<Account> emptyAccountList = (list<Account>)database.query(anotherQueryString);
system.assert(emptyAccountList.isEmpty());

And since you mentioned there being no equivalant to SELECT * FROM ... in SOQL, here's some code I've written to get around that. You'll need to dig into the code to see how it all works. It's very handy when starting a project.

    //=================   QUERY UTIL METHODS   =================================

//#################   Format Fields For Query   ###########################################
public static string FormatFieldsForQuery(sObject M, string prefix){
    if(string.isNotBlank(prefix)){
        if(prefix.endsWith('__c'))
            prefix=prefix.replace('__c','__r.');   
        if(!prefix.endsWith('.'))
            prefix+='.';
    }
    string fields = '';
    for(string f : M.getSObjectType().getDescribe().fields.getMap().keySet())
        fields+=prefix+f+',';
    return fields.removeEnd(',');
}//END FormatFieldsForQuery(sObject M, string prefix)

public static string FormatFieldsForQuery(sObject M){
    return FormatFieldsForQuery(M,'');
}//END FormatFieldsForQuery(sObject M)

public static string FormatFieldsForQuery(string objectName){
    return FormatFieldsForQuery(objectName,'');
}//END FormatFieldsForQuery(string objectName)

public static string FormatFieldsForQuery(string objectName,string prefix){
    if(string.isNotBlank(prefix)){
        if(prefix.endsWith('__c'))
            prefix=prefix.replace('__c','__r.');   
        if(!prefix.endsWith('.'))
            prefix+='.';
    }
    string fields = '';
    sObjectType objectType=Schema.getGlobalDescribe().get(objectName);
    if(objectType==null)
        return fields;
    for(string f :objectType.getDescribe().fields.getMap().keySet())
        fields+=prefix+f+',';
    return fields.removeEnd(',');  
}//END FormatFieldsForQuery(string objectName,string prefix)

public static string FormatFieldsForQuery(map<String,Schema.SObjectField> m,string prefix){ 
    if(string.isNotBlank(prefix)){
        if(prefix.endsWith('__c'))
            prefix=prefix.replace('__c','__r.');   
        if(!prefix.endsWith('.'))
            prefix+='.';
    }
    string fields = '';
    for(string f : m.keySet())
        fields+=prefix+f+',';
    return fields.removeEnd(',');
}//END FormatFieldsForQuery(map<String,Schema.SObjectField> m,string prefix)

public static string AllFieldsQuery(string objectName){
    return 'SELECT '+FormatFieldsForQuery(objectName)+' FROM '+objectName+' ';
}//END AllFieldsQuery

public static string AllFieldsQuery(sObject s){
    return 'SELECT '+FormatFieldsForQuery(s)+' FROM '+s.getSObjectType().getDescribe().getName()+' ';
}//END AllFieldsQuery

Test Code:

//#################   Format Fields For Query   ###########################################
@isTest private static void testFormatFieldsForQuery(){
    // Test order (depending upon input:
    //
    // #1 (sObject M, string prefix)
    // #2 (sObject M)
    // #3 (string objectName)
    // #4 (string objectName,string prefix)
    // #5 (map<String,Schema.SObjectField> m,string prefix)
    // #6 AllFieldsQuery(string objectName)   <---- different method name ----
    ///////////////////////////////////////////////////////////////////////////
    test.startTest();
    //test Account Standard Fields
    Account a=new Account();
    assertFFFQ(Portal.FormatFieldsForQuery(a,''),fffqTestObj.Account);
    assertFFFQ(Portal.FormatFieldsForQuery(a),fffqTestObj.Account);
    assertFFFQ(Portal.FormatFieldsForQuery('Account'),fffqTestObj.Account);
    assertFFFQ(Portal.FormatFieldsForQuery('Account',''),fffqTestObj.Account);
    assertFFFQ(Portal.FormatFieldsForQuery(Schema.SObjectType.Account.fields.getMap(),''),fffqTestObj.Account);
    assertFFFQ(Portal.AllFieldsQuery('Account'),fffqTestObj.Account);

    //test Contact Standard Fields
    Contact c=new contact();
    assertFFFQ(Portal.FormatFieldsForQuery(c,''),fffqTestObj.Contact);
    assertFFFQ(Portal.FormatFieldsForQuery(c),fffqTestObj.Contact);
    assertFFFQ(Portal.FormatFieldsForQuery('Contact'),fffqTestObj.Contact);
    assertFFFQ(Portal.FormatFieldsForQuery('Contact',''),fffqTestObj.Contact);
    assertFFFQ(Portal.FormatFieldsForQuery(Schema.SObjectType.Contact.fields.getMap(),''),fffqTestObj.Contact);
    assertFFFQ(Portal.AllFieldsQuery('Contact'),fffqTestObj.Contact);

    // bad objectName for 
    // #3 (string objectName)
    // #4 (string objectName,string prefix)
    system.assert(string.isBlank(Portal.FormatFieldsForQuery('This shouldn\'t work!!!')));

}//END testFormatFieldsForQuery2

public enum fffqTestObj {Account,Contact}

public static void assertFFFQ(string fieldString,fffqTestObj objName){
    set<string> fields=new set<string>(fieldString.removeStart('SELECT ').substringBefore(' FROM').split(','));

    if(objName==fffqTestObj.Account){
        system.assert(fields.contains('name'));
        system.assert(fields.contains('accountnumber'));
        system.assert(fields.contains('ownerid'));
        system.assert(fields.contains('site'));
        system.assert(fields.contains('accountsource'));
        system.assert(fields.contains('annualrevenue'));
        system.assert(fields.contains('createdbyid'));
        system.assert(fields.contains('jigsaw'));
        system.assert(fields.contains('description'));
        system.assert(fields.contains('numberofemployees'));
        system.assert(fields.contains('fax'));
        system.assert(fields.contains('industry'));
        system.assert(fields.contains('lastmodifiedbyid'));
        system.assert(fields.contains('ownership'));
        system.assert(fields.contains('parentid'));
        system.assert(fields.contains('phone'));
        system.assert(fields.contains('rating'));
        system.assert(fields.contains('sic'));
        system.assert(fields.contains('sicdesc'));
        system.assert(fields.contains('tickersymbol'));
        system.assert(fields.contains('type'));
        system.assert(fields.contains('website'));
    }
    else if(objName==fffqTestObj.Contact){
        system.assert(fields.contains('accountid'));
        system.assert(fields.contains('assistantname'));
        system.assert(fields.contains('assistantphone'));
        system.assert(fields.contains('birthdate'));
        system.assert(fields.contains('ownerid'));
        system.assert(fields.contains('createdbyid'));
        system.assert(fields.contains('jigsaw'));
        system.assert(fields.contains('department'));
        system.assert(fields.contains('description'));
        system.assert(fields.contains('donotcall'));
        system.assert(fields.contains('email'));
        system.assert(fields.contains('hasoptedoutofemail'));
        system.assert(fields.contains('fax'));
        system.assert(fields.contains('hasoptedoutoffax'));
        system.assert(fields.contains('homephone'));
        system.assert(fields.contains('lastmodifiedbyid'));
        system.assert(fields.contains('lastcurequestdate'));
        system.assert(fields.contains('lastcuupdatedate'));
        system.assert(fields.contains('leadsource'));
        system.assert(fields.contains('mobilephone'));
        system.assert(fields.contains('otherphone'));
        system.assert(fields.contains('phone'));
        system.assert(fields.contains('reportstoid'));
        system.assert(fields.contains('title'));  
    }
}//END assertFFFQ
Related Topic