2

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?

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>
  • N.B. I wouldn't put this in my production org, and neither should you. It requires quite a bit of work to be useful. This is only meant to be used as a getting started guideline. – sfdcfox Nov 18 '16 at 3:06
  • Thanks much! I got an idea now on how to go about this. I am creating a reusable component such that if <c:SampleComponent Record="Account"/> is used in any VF page then it will display the list of records of the object mentioned in Record=" ". I am planning to create a dynamic table where user can select the fields that they want to show in columns for any object. Do you think I am on right track in building this component? – SfdcBat Nov 18 '16 at 3:14
  • 2
    @SfdcBat Yes, you can just use describes to determine which fields to use based on the object, and all without ever casting to a "concrete" sobject type. Feel free to play with this code as a starting point. – sfdcfox Nov 18 '16 at 3:16
  • Thanks. Very much appreciated! I am planning to build something based on - developer.salesforce.com/docs/atlas.en-us.pages.meta/pages/… ( like the screenshot at the bottom of the page in the link) – SfdcBat Nov 18 '16 at 3:19
  • @SfdcBat Yeah, it shouldn't take more than a couple of hours to work out all the kinks. You're well on your way already. – sfdcfox Nov 18 '16 at 3:22
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);
    }
  • Nice!This is on the lines of what I am thinking but I would not know which type of List I have to create - List<Account> accList = new List<Account>();, It can be Contact/Lead or any custom object. – SfdcBat Nov 18 '16 at 2:35
  • There's no need to cast each individual record to a new list; you can simply assign it to a specific type (it doesn't even require a cast!): Sobject[] records = Database.query('select id, name, ownerid from '+objectName+' limit 10'); if(records.getSObjectType()==Account.SObjectType) { Account[] accounts = records; } – sfdcfox Nov 18 '16 at 2:44
  • Thanks, What happens if I have a custom object or Lead records that I is returned in the records? Do I need use if condition for each type of object? – SfdcBat Nov 18 '16 at 3:01
  • @SfdcBat Check out my answer that demonstrates a very primitive "show any table" example. – sfdcfox Nov 18 '16 at 3:02
  • My apologies, maybe I was mistaken or this is a recent addition but you don't need to parse the records individually. sfdcfox is correct. – Nonick Nov 18 '16 at 3:54
1

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.

  • This assumes that controllerValue is type String, which not the type in the OP's code. Also note that you will need to put spaces around the name of the object. – Jeremy Nottingham Nov 18 '16 at 1:39
  • If controllerValue is a type of String , how can assign the result of the query which is a list of record with type Account/Contact/Lead(controllerValue type) ? I am trying to make it dynamic. – SfdcBat Nov 18 '16 at 1:44
  • salesforce.stackexchange.com/questions/118656/… 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. – Nonick Nov 18 '16 at 2:03
  • @SfdcBat can you make it an SObject, not an object. In that way, we can probably easily walk around. – Lance Shi Nov 18 '16 at 3:06
  • @LanceShi I should make the attribute of type String and the records which are returned should be of type Sobject. I took what sfdcfox gave as an answer and what you gave as an answer and combined it. I guess I got an idea now on how to go about it. – SfdcBat Nov 18 '16 at 3:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.