Include apex variable in SOQL query

Question

I have a visualForce page and an apex controller. I need to get a date value from my visualForce page into my Apex Controller (this is done already) and then pass this value into my SOQL query. My SOQL query runs fine when I put an actual date value in, but when I use the variable created before it says can't find variable named x.

I'm certain this is a general programming mistake, this is only my second week using code not clicks!

Let me know if you want to see the VF page too.

**Apex Controller***

global with sharing class GoogleChartsController2 {
    Opportunity o = new Opportunity();
    public Opportunity getProxyObject() {
        return o;
    }

    /**
      Loads all Opportunities and then filters 
    */
    @RemoteAction   
    global static Opportunity[] loadOpps() {
        return [select Id, Name, ExpectedRevenue, LeadSource, DaysToStartDate__c, Probability,   CloseDate, Amount from Opportunity WHERE CloseDate = :o.CloseDate ];
    }   
}

Section from VisualForce Page *

<apex:page controller="GoogleChartsController2" sidebar="false"> 
<!-- Google API inclusion -->
<apex:includescript id="a" value="https://www.google.com/jsapi">

<apex:sectionheader title="Google Charts + Javascript Remoting" subtitle="Demoing - Opportunities  by Exepected Revenue">

<!-- Google Charts will be drawn in this DIV -->
<div id="chartBlock" style="width: 900px; height: 500px;">>

<script type="text/javascript">
    // Load the Visualization API and the piechart package.
    google.load('visualization', '1.0', {'packages':['corechart']});

    // Set a callback to run when the Google Visualization API is loaded.
    google.setOnLoadCallback(initCharts);

    function initCharts() {         
      // Following the usual Remoting syntax
      // [<namespace>.]<controller>.<method>([params...,] <callbackFunction>(result, event) {...}
      // controller : GoogleChartsController
      // method : loadOpps
      GoogleChartsController2.loadOpps( 
             function(result, event){

                 // load Column chart
                 var visualization = new    google.visualization.BubbleChart(document.getElementById('chartBlock'));
                 // Prepare table model for chart with columns
                 var data = new google.visualization.DataTable();
                 data.addColumn('string', 'Opportunity');             
                 data.addColumn('number', 'Days To Contract Start');
                 data.addColumn('number', 'Probability (%)');
                 data.addColumn('string', 'Lead Source');  
                 data.addColumn('number', 'Amount');  
                 // add rows from the remoting results
                 for(var i =0; i<result.length;i++){
                    var r = result[i];
                    data.addRow([r.Name, r.DaysToStartDate__c, r.Probability, r.LeadSource, r.Amount]); 
                  }
                // all done, lets draw the chart with some options to make it look nice.
                visualization.draw(data, {
                    legend : {position: 'right', textStyle: {color: 'black', fontSize: 10}},                         
                    width:window.innerWidth,
                    length:window.innerLength,
                    vAxis:{title: 'Probability', textStyle:{fontSize: 10}, minValue: 0, maxValue: 100},
                    hAxis:{title: 'Days Until Close' , textStyle:{fontSize: 10}, 
                    chartArea: {width: '50%', height: '75%'},  
                    sizeAxis: {minSize: 150, minValue: 150},
                    showTextEvery:1,slantedText:false}})
                  }, 
                  {escape:true});

      } 
</script>

</div></apex:sectionheader></apex:includescript>

 <apex:form >
      <apex:outputlabel value="Enter your name here"/>
      <apex:inputField value="{!proxyObject.closeDate}"/>
      <apex:actionsupport event="onclick" rerender="display" />   

      <apex:outputpanel id="display">
       <apex:outputtext value="The date entered is {!proxyObject.closeDate}"/>
      </apex:outputpanel>            
</apex:form>


</apex:page>

Answer 1

The need for remoting makes this a bit more interesting. Because remoting methods must be declared as static (as you've already done), you won't be able to reference any member variables that you're using in the controller. You can, however, pass along their value from the page in your remoting call.

First off, you'll want to modify your remoting method to accept a parameter from the Javascript that's calling it. Then you'll just parse that date string in your SOQL query. You can leave the proxyObject in the controller in order to leverage the out-of-the-box date picker that Salesforce provides, but you won't be referencing that variable directly in the loadOpps method.

global with sharing class GoogleChartsController2 {
    public Opportunity proxyObject {public get; public set;}

    public GoogleChartsController2() {
        proxyObject = new Opportunity();
        // just giving this a value for testing purposes
        proxyObject.CloseDate = System.today();
    }

    /** Loads all Opportunities and then filters */
    @RemoteAction   
    global static Opportunity[] loadOpps(String closedate) {
        return [select Id, Name, ExpectedRevenue, LeadSource, Probability, CloseDate, Amount from Opportunity WHERE CloseDate = :Date.parse(closedate)];
    }
}

Next, you're going to have to reference the field on your VF page when making the call to loadOpps. You can do this a couple of different ways, the easiest of which for these circumstances is probably to use the VF $Component merge field markup. This will require you to specify IDs for your nested VF tags:

<apex:form id="testForm" >
    <apex:inputField id="closedate" value="{!proxyObject.closeDate}"/>

And these can then be referenced like:

var dt = document.getElementById("{!$Component.testForm.closedate}").value;

So your final VF page becomes something like:

<apex:page controller="GoogleChartsController2" sidebar="false"> 
    <apex:sectionheader title="Google Charts + Javascript Remoting" subtitle="Demoing - Opportunities  by Expected Revenue" />

    <apex:form id="testForm" >
        <apex:outputlabel value="Enter a date here"/>
        <apex:inputField id="closedate" value="{!proxyObject.closeDate}" >
            <apex:actionsupport event="onchange" rerender="display" />
        </apex:inputField>
        <apex:outputpanel id="display">
            <apex:outputtext value="The date entered is {!proxyObject.closeDate}"/>
        </apex:outputpanel>            
    </apex:form>

    <!-- Google API inclusion -->
    <apex:includescript id="a" value="https://www.google.com/jsapi">

    <!-- Google Charts will be drawn in this DIV -->
    <div id="chartBlock" style="width: 900px; height: 500px;">>

        <script type="text/javascript">
            // Load the Visualization API and the piechart package.
            google.load('visualization', '1.0', {'packages':['corechart']});

            // Set a callback to run when the Google Visualization API is loaded.
            google.setOnLoadCallback(initCharts);

            function initCharts() {         
              // Following the usual Remoting syntax
              // [<namespace>.]<controller>.<method>([params...,] <callbackFunction>(result, event) {...}
              // controller : GoogleChartsController
              // method : loadOpps
              var dt = document.getElementById("{!$Component.testForm.closedate}").value;
              GoogleChartsController2.loadOpps( dt, 
                     function(result, event){

                         // load Column chart
                         var visualization = new    google.visualization.BubbleChart(document.getElementById('chartBlock'));
                         // Prepare table model for chart with columns
                         var data = new google.visualization.DataTable();
                         data.addColumn('string', 'Opportunity');
                         data.addColumn('number', 'Days To Contract Start');
                         data.addColumn('number', 'Probability (%)');
                         data.addColumn('string', 'Lead Source');  
                         data.addColumn('number', 'Amount');  
                         // add rows from the remoting results
                         for(var i =0; i<result.length;i++){
                            var r = result[i];
                            data.addRow([r.Name, r.DaysToStartDate__c, r.Probability, r.LeadSource, r.Amount]); 
                          }
                        // all done, lets draw the chart with some options to make it look nice.
                        visualization.draw(data, {
                            legend : {position: 'right', textStyle: {color: 'black', fontSize: 10}},                         
                            width:window.innerWidth,
                            length:window.innerLength,
                            vAxis:{title: 'Probability', textStyle:{fontSize: 10}, minValue: 0, maxValue: 100},
                            hAxis:{title: 'Days Until Close' , textStyle:{fontSize: 10}, 
                            chartArea: {width: '50%', height: '75%'},  
                            sizeAxis: {minSize: 150, minValue: 150},
                            showTextEvery:1,slantedText:false}})
                          }, 
                          {escape:true});

              } 
        </script>

    </div></apex:includescript>
</apex:page>

This seems to work for the most part for me, although the charting API is complaining about Column 2 being a string...but that's something you should be able to sort out by changing your columns around. Keep in mind that this currently won't re-render the chart whenever you change the date, but that shouldn't be difficult if you make a few changes to your apex:actionSupport tag.

Hope this gets you on the right track!