Using a variable after FROM in SOQL statement

Question

I am building a custom VF component where I have an attribute which of type Object. I use this attribute to query first 10 records and display it using pageblocktable. The user can provide any object name like Account/contact/lead in the attribute and the first 10 records of that object gets displayed in a VF page.

I need some help on how I can use the variable name in the SELECT query after from. For example - [select field1 from variableName Limit 10]. Instead of a Sobject name I am giving a variable which has the Sobject name in it.

Below is my incomplete code, I am trying to make it dynamic. Right now I have used Accounts in my controller but I would like to use the variable - controllerValue where ever there is Account in my controller.

VF component:

apex:component controller="SampleComponentController" >

    <apex:attribute name="record" description="The type of record we are viewing."
                    type="Object" required="true" assignTo="{!controllerValue}"/>

    <apex:pageBlock title="Viewing {!record}">   

    </apex:pageBlock>

<apex:pageblock >
<apex:pageblocktable value="{!Records}" var="a">

<apex:column value="{!a.Name}"/>

</apex:pageblocktable>
</apex:pageblock>
</apex:component>

VF component controller:

public class SampleComponentController {

public object controllerValue;

private ApexPages.StandardSetController setController{
        get {
            if(setController==NULL){
            system.debug('******Component***********' + controllerValue);
                List<account> accountList = [SELECT Name,BillingAddress,Phone FROM Account LIMIT 20];

// I would like the above query to be dynamic:
// List<controllerValue> accountList = [SELECT Name,BillingAddress,Phone FROM :controllerValue LIMIT 20];

                setController = new ApexPages.StandardSetController(accountList);

            }
            return setController;
        }
        set;
    }

    public List<Account> getRecords(){
        return (List<Account>) setController.getRecords();
    }

   public void setControllerValue (Object s) {
    controllerValue = s;
  }

  public Object getControllerValue() {
    return controllerValue;
  } 

}

I am planning to use it in VF Page as:

<apex:page controller="DynamicTableController">

<c:SampleComponent Record="Account"/>    

</apex:page>

I tried to use the dynamic query like:

List<account> accountList = Database.query('SELECT Name,BillingAddress,Phone FROM :controllerValue LIMIT 20');

I got this error when I saved the Visualforce Page :

Visualforce Error

System.QueryException: unexpected token: ':' 
Class.SampleComponentController.__sfdc_setController: line 9, column 1
Class.SampleComponentController.getRecords: line 21, column 1

Any Suggestions?

Answer 1

You don't need to perform the cast, as you can just use dynamic Visualforce field binding. I wrote up this example just now that demonstrates the basic idea. This is more meant to be instructional than functional, but it does work in my developer org.

public class QueryAnyTableController {
    public String selectedType { get; set; }

    public SelectOption[] getObjectOptions() {
        SelectOption[] options = new SelectOption[0];
        for(SObjectType sobjType: Schema.getGlobalDescribe().values()) {
            DescribeSObjectResult describe = sobjType.getDescribe();
            options.add(new SelectOption(describe.getName(), describe.getLabel()));
        }
        return options;
    }
    public SObject[] getRecordList() {
        try {
            return Database.query('select Name'+(getAllowsOwner()?', OwnerId':'')+ ' FROM '+selectedType+' LIMIT 10');
        } catch(Exception e) {
            return new SObject[0];
        }
    }
    public Boolean getAllowsOwner() {
        try {
            return Schema.getGlobalDescribe().get(selectedType).getDescribe().fields.getMap().containsKey('ownerid');
        } catch(Exception e) {
            return false;
        }
    }
}

---

<apex:page controller="QueryAnyTableController">
    <apex:form id="form">
        <apex:pageBlock>
            <apex:pageBlockButtons location="top">
                <apex:selectList value="{!selectedType}" size="1">
                    <apex:actionSupport event="onchange" reRender="form" />
                    <apex:selectOptions value="{!objectOptions}" />
                </apex:selectList>
            </apex:pageBlockButtons>
            <apex:pageBlockTable value="{!recordList}" var="rec">
                <apex:column value="{!rec['Id']}" />
                <apex:column value="{!rec['Name']}" />
                <apex:column rendered="{!allowsOwner}" value="{!rec['OwnerId']}" />
            </apex:pageBlockTable>
        </apex:pageBlock>
    </apex:form>
</apex:page>

Answer 2

SOQL Dynamic Query on objects ( Dynamic sobject name in soql ) this is probably along the lines of what you're looking for, but you're going to query for an 'sobject' then cast the sobject to a specific type. You'll be running a query for:

List<sObject> listSObject = Database.query('select id, name, ownerId from ' + blah + 'where blah2');
List<Account> accList = new List<Account>();
    if(blah == 'Account'){
        for(sObject genericRecord : listSObject)
            accList.add((Account)genericRecord);
    }

Answer 3

You can use

database.query('SELECT Name,BillingAddress,Phone FROM' + controllerValue + 'LIMIT 20');

You need to consider preventing soql injection though. However, it doesn't mean you can't use String + variable in your dynamic SOQL at all.