How to display an SOQL query in apex Datatable

Question

I'm having a difficult time finding what (I think) should be a relatively simple procedure. I would like to create a class controller that returns a SOQL query and then display the results in an apex datatable on a visualforce page. I would then like to display that visualforce page on a dashboard.

In my org I have Contacts and Opportunities related with contact roles. Opportunities have a custom child object that defines what program an opportunity belongs to. An opportunity can belong to multiple programs. This object is called 'targeting allocation'. I'm trying to display the [opportunity.account|contactrole.name|role|opportunity.principal_balance] in a datatable.

I know my SOQL query works:

[select opportunity.account.name, contact.name, role, opportunity.name, >>opportunity.current_principal_balance__c from opportunitycontactrole where opportunityid in (select allocation_opportunity__c from targeting_allocation__c where program_type__c = 'Green Initiative') and opportunity.current_principal_balance__c > 0 and isprimary = true]

I simply don't know how to put it into a class and then display. Its seemingly impossible to find good simple tutorials on classes and apex pages.

EDIT: Thank you crmprogdev and Jagular for your links. Through your links I found this page: What is wrapper class?? on success.salesforce.com.

I followed the example there and came up with this:

Wrapper Class

public class GreenInvestorWrapper {
    public class TableRow {
        public string AccountName   {get; set;}
        public string ContactName   {get; set;}
        public string ContactRole   {get; set;}
        public string oppName       {get; set;}
        public decimal oppValue     {get; set;}
    }
    public list  RowList {get; set;}
    public GreenInvestorWrapper () {
        RowList = new List();
        TableRow tr;
        For (OpportunityContactRole x: 
            [SELECT opportunity.account.name, contact.name, role, opportunity.name,opportunity.current_principal_balance__c FROM opportunitycontactrole WHERE opportunityid in (SELECT allocation_opportunity__c FROM targeting_allocation__c >WHERE program_type__c = 'Green Initiative') AND >opportunity.current_principal_balance__c > 0 AND isprimary = true])
            {
                tr = new TableRow();
                tr.AccountName = x.opportunity.account.name;
                tr.contactName = x.contact.name;
                tr.contactRole = x.role;
                tr.oppName = x.opportunity.name;
                tr.oppValue = x.opportunity.current_principal_balance__c;
                RowList.add(tr);
            }
    }
}

Apex Page

<apex:page controller="GreenInvestorWrapper">
    <apex:pageBlock >
        <apex:pageBlockTable value="{!RowList}" var="row">
            <apex:column value="{!row.AccountName}"/>
            <apex:column value="{!row.ContactName}"/>
            <apex:column value="{!row.ContactRole}"/>
            <apex:column value="{!row.oppName}"/>
            <apex:column value="{!row.oppValue}"/>
        </apex:pageBlockTable>

    </apex:pageBlock>
</apex:page>

Sorry for formatting!

My next goal is to make this a list that is filterable by program_type__c, which is a field on the targeting allocation.

Thank you for all the help!

Answer 1

I recommend the following resources Visualforce Workbook and the Visualforce Developer’s Guide. You should find what you need in the above. Once you've written some code, come back, edit your message to insert your code and tell us exactly where you're having problems. If the resources above don't provide sufficient tutorials for you, try using Find SFInfo to search for others.

Answer 2

There is a good example of a Visualforce page in use with a wrapper class here.

This example uses a PageBlockTable which you may find easier to use than DataTable. The PageBlockTable takes on the visual styling of Salesforce; the Datatable does not.

Answer 3

public with sharing class opportunityExt {
    private ApexPages.StandardController standardController;
    private ApexPages.StandardSetController setController;  
    public opportunityExt(ApexPages.StandardController  controller) 
    {        
        this.standardController = controller;   
        doQuery();
    }    


    public list <opportunity> getopportunityRecords()
    {                     
        return (list<opportunity>) setController.getRecords();
    }

    public void doQuery()
    {       

          String qs=[select opportunity.account.name, contact.name, role, opportunity.name, >>opportunity.current_principal_balance__c from opportunitycontactrole where opportunityid in (select allocation_opportunity__c from targeting_allocation__c where program_type__c = 'Green Initiative') and opportunity.current_principal_balance__c > 0 and isprimary = true];
          //initialize query locator
          Database.QueryLocator ql = Database.getQueryLocator(qs);        

          //initialize set controller
          this.setController = new ApexPages.StandardSetController(ql);  

    }          

}

<apex:page standardController="opportunity" extensions="opportunityExt ">

        <apex:pageBlock title="">
             <apex:pageBlockSection  >

                    <apex:dataTable value="{!opportunityRecords}" var="opp" id="theTable" >          

                         <apex:column>
                                <apex:facet name="header">
                                        <apex:outputLabel value="opportunity.account.name"/>                                         
                                  <apex:param value="opportunity.account.name"/>                             
                                    </apex:outputPanel>                                                          
                                </apex:facet>
                                <apex:outputText value="{!opp.account.name}"/>
                        </apex:column>

            </apex:dataTable>
        </apex:pageBlockSection>
    </apex:pageBlock>