Can a SOQL query selecting all columns be generated at runtime?

Question

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 ...];

Scott Pelak · Accepted Answer · 2014-01-29 17:54:06Z

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

sfdcfox · Accepted Answer · 2014-01-29 18:00:53Z

For performance reasons, you shouldn't try to be using a wildcard field query. That said, there are legitimate reasons why you would want to do so, and for that, I generate my queries like this:

public String queryFromSObject(SObjectType entity) {
    return 'select '+
        string.join(new list<string>(entity.getdescribe().fields.getmap().keyset()),',')+
        ' from '+string.valueof(entity);
}

As for "converting to a string type", there's no need for this, if you're willing to place yourself within certain limitations:

public SObject[] queryResults(SObjectType entity, String fn1, Object v1, String fn2, Object v2) {
    return Database.query(queryFromSObject(entity)+' where '+fn1+' and '+fn2);
}

Usage:

queryResults(Account.SObjectType, 'CreatedDate < :v1', DateTime.Now(), 'Name IN :v2', new String[] { 'Acme' });

Due to the lack of dynamic parameter lengths and binding to individual array elements, you have to accept the fact that you'll need to make as many Object references as necessary in your function parameters to support each of the various number of parameters.

I'll leave this up to your imagination, but the point is as long as you can bind to a local variable, you don't need to convert to a string each time.

Your usage section shows something that I was not familiar with. 'Name IN :v2'. Does :v2 do some sort of String interpolation? — edgartheunready, Jan 29, 2014 at 18:06
@edgartheunready It's SOQL dynamic binding. Only works on simple variables inside a string though (so not like an eval type thing), but it does handle automatic variable replacements during query execution time (i.e. they are just normal strings until called by Database.query, at which point they are dynamically bound). The variable names have to exist in the current scope or they won't bind. — sfdcfox, Jan 29, 2014 at 18:07

dzh · Accepted Answer · 2020-05-09 04:12:50Z

Not really answer to your question, but I found this pattern works well for past few years. I just dump all the fields into selector class/method.

It's easy to do using Illuminated Cloud, just type [SEL*] and it will give you option to select object with all it's columns.

Of course over time you need to add new fields there and some system fields can be unavailable to certain users. But at least you've got some sort of metadata binding when deleting/updating field type.

Community · Accepted Answer · 2017-04-13 12:54:24Z

1

This question is very similar to Howto wildcard SELECT * to query all fields of objects in APEX?

In short: I created a solution similar to @AmatorVitae possibly with some more features. The source code is available here: https://github.com/UweHeim/xt

It supports stuff like this:

select * from Opportunity
select *, Account.* from Opportunity
select *, CreatedBy.*, Account.Owner.* from Opportunity 
select *, YourCustomRelatedField__r.* from WhatEver__c
select *, Even__r.Very__r.Distant__r.Objects__r.Are__r.Supported__r.* from WhatEver__c
select *.not(Name|Probability|YourCustomField__c) from Opportunity

For further details have a look at the first link to the other question above or try it out for yourself.

edited Apr 13, 2017 at 12:54

CommunityBot

1

answered Sep 18, 2014 at 11:09

Uwe Heim

28.1k13 gold badges114 silver badges277 bronze badges

1

+1. I would like to know why this answer got a down vote. For those looking for more details of this answer, take a look at the other answer here
– lindon fox
Jul 15, 2015 at 11:49

Add a comment |

Andrii Muzychuk · Accepted Answer · 2016-02-11 21:29:11Z

Why

public static List<sObject> getListWAllFields(string objectName, string whereClaus, integer limitNumber) {
        // Initialize setup variables
        //String objectName = 'Contact';  // modify as needed
        String query = ' SELECT ';
        Map<String, Schema.SObjectField> objectFields = Schema.getGlobalDescribe().get(objectName).getDescribe().fields.getMap();

    // Grab the fields from the describe method and append them to the queryString one by one.
    for(String s : objectFields.keySet()) {
       query += ' ' + s + ', ';
    }

  query = query.subString(0,query.Length()-2);

    // Add FROM statement
    query += ' FROM ' + objectName;

    // Add on a WHERE statement as needed
    if (String.isNotBlank(whereClaus)) {
      query += ' WHERE ' + whereClaus;
    }
    // Add on a LIMIT statement as needed
    if (String.isNotBlank(whereClaus)) {
      query += ' LIMIT ' + String.valueOf(limitNumber);
    }

    try {
      return database.query(query);
    } catch (QueryException qe) {
      System.debug(LoggingLevel.ERROR, '\n\n --- getListWAllFields --- '
        +'\n - objectName: ' + objectName
        +'\n - whereClaus: ' + whereClaus
        +'\n - query: ' + query
        +'\n - qe: ' + qe
    +'\n - qe: ' + qe.getMessage()
    +'\n - qe.getLineNumber: ' + qe.getLineNumber()
    +'\n - qe.getStackTraceString: ' + qe.getStackTraceString()
        +'\n'
      );
  return null;
    }
}

with

List<Opportunity> opps = (List<Opportunity>)Opportunity_Manager.getListWAllFields(
 'Opportunity',
 null,
 2
);

System.debug(LoggingLevel.DEBUG, '\n - opps: ' + opps
 +'\n'
);

does not work for you? You have all fields in any object. And you have normal list of standard or custom objects. At the end of the day, you will have your List. What is wrong with 'string' method? However, i didn't get

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

part of the question.

Stack Exchange Network

Can a SOQL query selecting all columns be generated at runtime?

5 Answers 5

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged
apex
soql
dynamic-soql
query
.

Linked

Hot Network Questions

Can a SOQL query selecting all columns be generated at runtime?

5 Answers 5

You must log in to answer this question.

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

Linked

Related

Hot Network Questions

Not the answer you're looking for? Browse other questions tagged
apex
soql
dynamic-soql
query
.