How would I get values from a standard controller form in soql query?

Question

So I have built an apex page as follows...

    <apex:pageBlockSection >
                <apex:inputField value="{!Tree__c.Tree_Type__c}" />
                <apex:inputField value="{!Tree__c.Diseases__c}" />
            </apex:pageBlockSection>

  <apex:outputPanel >
                    <apex:commandButton value="Search" action="{!doSearch2}"
                        reRender="block" status="status" />
                </apex:outputPanel>

And I have built a controller as follows (I am trying to get a few fields working to start with, hence the code comments...

           soql = 'Select Tree_Type__c, Tree_Surgeon__c, Diseases__c, Status__c, Date_of_Planting__c, Native_breed__c from Tree__c WHERE';
    if (!varTreeType.equals(''))
      soql += ' Tree_Type__c LIKE "+varTreeType+"';

  //  if (!lastName.equals(''))
  //    soql += ' and lastname LIKE "+lastName+"';

  //  if (!accountName.equals(''))
  //    soql += ' and account.name LIKE "+accountName+"';

  //  if (!technology.equals(''))
  //    soql += ' and interested_technologies__c includes ("+technology+")';

How do I get the values of the VisualForce form fields, and use them within the query?

Answer 1

Assuming you are using the tree fields because they are values such as picklists, and that you are using a controller extension, this code will build the dynamic query making use of bind variables which are safe from SOQL injection attack:

private ApexPages.StandardController sc;

public MyController(ApexPages.StandardController sc) {
    this.sc = sc;
}

private void yourQueryMethod() {

    Tree__c filter = (Tree__c) sc.getRecord();

    String[] wheres = new String[] {};
    String treeTypeLike;
    String diseasesLike;
    ...
    if (filter.Tree_Type__c != null) {
        wheres.add('Tree_Type__c LIKE :treeTypeLike');
        treeTypeLike = '%' + filter.Tree_Type__c + '%';
    }
    if (filterDiseases__c != null) {
        wheres.add('Diseases__c LIKE :diseasesLike');
        diseasesLike = '%' + filter.Diseases__c + '%';
    }
    ...
    String soql = 'Select ... from Tree__c'
            + (wheres.size() > 0 ? ' WHERE ' : '')
            + String.join(wheres, ' AND ');
    ....
}

Answer 2

Try removing the double quotes like this :

soql = 'Select Tree_Type__c, Tree_Surgeon__c, Diseases__c, Status__c, Date_of_Planting__c, Native_breed__c from Tree__c WHERE';
    if (!varTreeType.equals(''))
      soql += ' Tree_Type__c LIKE ' + varTreeType;

Answer 3

You could use

<apex:inputField value="{!treeType}" />
<apex:inputField value="{!diseases}" />

in your Visualforce page. Do not forget to make properties in your controller class:

public String treeType { get; set; }
public String diseases { get; set; }

and this will change values of your properties depending on your text in your inputFields. Then you could build a query like that:

soql = 'SELECT Tree_Type__c, Tree_Surgeon__c, Diseases__c, Status__c, Date_of_Planting__c, Native_breed__c FROM Tree__c WHERE';
if (!treeType.equals(''))
  soql += ' Tree_Type__c LIKE \'' + treeType + '\'';

Note the \'. Backslash and single quote will force your string to have a single quote inside it without closing the string(for more info read about Escape Character).

Answer 4

Your form will need a few text boxes like this:

<form>
<apex:pageBlock>
 <apex:pageBlockButtons>
  <apex:commandButton action="{!doSearch}" value="Search"/>
 </apex:pageBlockButtons>
 <apex:pageBlockSection>
  <apex:pageBlockSectionItem>
    <apex:outputLabel value="Tree Type"/>
    <apex:inputText value="{!varTreeType}" />
  </apex:pageBlockSectionItem>
  <apex:pageBlockSectionItem>
    <apex:outputLabel value="Last Name"/>
    <apex:inputText value="{!lastName}" />
  </apex:pageBlockSectionItem>
  etc...
 </apex:pageBlockSection>
</apex:pageBlock>  
</form>

You will then need some properties in your controller that will bind to these:

public String varTreeType {get;set;}
public String lastName {get;set;}

The doSearch in the command button would just bind to the method you've already created that is invoking the string search. One important thing, though is that you want to make sure to prevent SOQL injection. So a safer implementation of these properties would be:

public String varTreeType {
  get;
  set {
    varTreeType = String.escapeSingleQuotes(value);
  }
}