Apex SOQL subquery in Visualforce

Question

I am looking at showing a subquery SOQL query in an Visualforce page. This is my SOQL Expression.

public ApexPages.StandardSetController setCon {
        get {
            if(setCon == null) {
                setCon = new ApexPages.StandardSetController(Database.getQueryLocator(
                    [SELECT Contact.Id, Opportunity.Id, Contact.FirstName, Contact.LastName, Contact.Phone,Contact.Account.Name, Contact.Email,Contact.Last_Contacted__c,Contact.Membership_Type__c,Opportunity.Call_Disposition__c, Opportunity.Sales_Stage__c,Opportunity.Name, Opportunity.CloseDate, Opportunity.StageName, Opportunity.CreatedDate FROM OpportunityContactRole where Opportunity.OwnerId=:Userinfo.getUserId()]));
            }
            return setCon;
        }
        set;
    }

It got a message 'OpportunityContactRole is not supported in StandardSetController'. So I tried to get Opportunity and Contact info from Account...

So my SOQL query changed to:

SELECT Name, (SELECT Name, Phone, Email, Last_Contacted__c, Contact.Membership_Type__c FROM Account.Contacts) , (SELECT Call_Disposition__c, StageName, CreatedDate, CloseDate FROM Account.Opportunities) FROM Account where Id=:UserInfo.getUserId()])

but now in my Visualforce page I am not able to access the subquery fields:

<apex:page controller="SalesRepPageControllerV3" tabstyle="contact" sidebar="false" showChat="true" >
   <apex:form id="theForm">
    <apex:sectionHeader title="Sales Rep Page for {!$User.FirstName}"/>
      <apex:pageBlock id="innerblock" mode="edit"> 
         <apex:pageMessages />

        <apex:pageBlock id="innerblock">  
        <apex:pageBlockSection id="pagesection">  
            <apex:pageBlockTable value="{!ContactOpportunity}" var="co" id="pageblocktable">
              <apex:column headerValue="Created Date" value="{!co.Opportunity.CreatedDate}"/>  
              <apex:column headerValue="First Name" value="{!co.Contact.FirstName}"/>  
              <apex:column headerValue="First Name" value="{!co.Contact.LastName}"/>
              <apex:column headerValue="Phone" value="{!co.Contact.Phone}"/>
              <apex:column headerValue="Account Name" value="{!co.Contact.Account.Name}"/>
              <apex:column headerValue="Email" value="{!co.Contact.Email}"/>
              <apex:column headerValue="Last Contacted" value="{!co.Contact.Last_Contacted__c}">
              </apex:column>

                <apex:column headerValue="Membership Type" value="{!co.Contact.Membership_Type__c}"/>
                <apex:column headerValue="Call Disposition">
                    <apex:inputField value="{!co.Opportunity.Call_Disposition__c}"/>
                </apex:column>
                <apex:column headerValue="Sales Stage">
                    <apex:inputField value="{!co.Opportunity.Sales_Stage__c}"/>
                </apex:column>         
            </apex:pageBlockTable>
           </apex:pageBlockSection>

        </apex:pageBlock>
        <apex:pageBlockButtons >
           <apex:commandButton value="Save" action="{!save}" reRender="pageblocktable"/>
           <apex:commandButton value="Cancel" action="{!cancel}"  reRender="pageblocktable"/>
        </apex:pageBlockButtons>  
     </apex:pageBlock>
     <apex:panelGrid columns="2">
              <apex:commandLink action="{!previous}">Previous</apex:commandlink>
              <apex:commandLink action="{!next}">Next</apex:commandlink>
    </apex:panelGrid>
    </apex:form>   
</apex:page>

It does not understand the co.Opportunity and co.Contact as it is not a Account. And if I change it to co.Opportunities or co.Contacts it is an Arraylist which can only be accessed by repeat.

The problem with apex:repeat is it does not have column header values which I need to sort by. Please if someone can help please let me know how.

metadaddy · Accepted Answer · 2012-08-20T16:46:55.180

3

Heavily edited...

I don't think you can use a standard set controller for this. Using a custom controller I was able to get a consolidated list of opportunity contacts for the current user:

enter image description here

Page:

<apex:page controller="TestQueryController">
  <apex:form >
    <apex:pageBlock >
        <apex:pageBlockTable value="{!opportunities}" var="co">
            <apex:column value="{!co.Opportunity.CreatedDate}"/>
            <apex:column value="{!co.Contact.FirstName}"/>
            <apex:column value="{!co.Contact.LastName}"/>
            <apex:column headerValue="Stage">
                <apex:inputField value="{!co.Opportunity.StageName}"/>
            </apex:column> 
        </apex:pageBlockTable>
    </apex:pageBlock>
  </Apex:form>
</apex:page>

Controller:

public with sharing class TestQueryController {
    List<OpportunityContactRole> myOCR;

    public List<OpportunityContactRole> getOpportunities() {
        if (myOCR == null) {
            myOCR = [SELECT Contact.Id, Opportunity.Id, Contact.FirstName, Contact.LastName, Contact.Phone,
                Contact.Account.Name, Contact.Email, Opportunity.Name, 
                Opportunity.CloseDate, Opportunity.StageName, Opportunity.CreatedDate 
                FROM OpportunityContactRole where Opportunity.OwnerId=:Userinfo.getUserId()];
        }
        return myOCR;
    }
}

You will need to substitute in your custom fields. If you need sorting, this blog post gives one approach, though you can probably avoid repeat trips to the database using a wrapper class and the Apex Comparable interface. Finally, you'll need to implement your own save logic. Good luck!

edited Aug 20 '12 at 16:46

answered Aug 17 '12 at 21:54

metadaddy

4,234
1
22
46

It works but I am looking to put both the contact and opportunity in one pageBlockTable which I can then sort by. Is that possible?Is there any other alternative todo this? – Thys Andries Michels Aug 18 '12 at 03:51
I see your actual requirement now in your question - I was focused on making Gerard's code work. Leave it with me - I'll have a think about how to do this and maybe post something next week. – metadaddy Aug 18 '12 at 14:31
Hey thanks guys, I was only able to test the query on Workbench but the visual force code. It was an assumption that the nested blocks would work. Half of visual force programming is guessing, isn't it! – Gerard Sexton Aug 18 '12 at 15:58
Hi Pat, thanks for responding, a solution will be great as I need to deliver this project next week. I also need to be able to save changes made to the inputfield. As I am doing a inner SOQL query for opportunity and contact from account it looks like it doesn't want to save the changes when calling setcon.save() where setCon is of Type ApexPages.StandardSetController. – Thys Andries Michels Aug 19 '12 at 03:28
@ThysAndriesMichels - I don't think you can use a standard set controller - see my edited answer. – metadaddy Aug 20 '12 at 16:47
Hi Pat, funny but this is how I have done it in the beginning. The problem I had was saving the Opportunity input field change. I tried to use the StandardSetController but got this error msg: List controllers are not supported for OpportunityContactRole. How will I go about saving the opportunity input field from OpportunityContactRole object? – Thys Andries Michels Aug 20 '12 at 19:18
You can iterate through the list of OpportunityContactRoles, building a collection of Opportunities that you then update. The problem, though, is the page design. You have the same items of opportunity data (call disposition and sales stage) on multiple rows in the table. This means that, if the user updates one of them, you have to keep track of which opportunity (in your list of OpportunityContactRoles) has the most recent data. It gets worse - what if the user updates call disposition for one and sales stage for the other? – metadaddy Aug 20 '12 at 21:41
How will I keep track of those two fields maybe have an onchange action and put the values in a Map>?To overcome this problem and to have the capability to use the StandardSetController I am thinking of using some of the fields in account and make the account linkable so sales reps can get the contact name from there. Thoughts? – Thys Andries Michels Aug 21 '12 at 00:39
Those two fields seem to be per-Opportunity, so maybe link to Opportunity from each OpportunityContactRole row? – metadaddy Aug 21 '12 at 01:06
I settled for a work around. Will post my result when I am done. Thanks for all your help Pat! – Thys Andries Michels Aug 24 '12 at 06:31

Gerard Sexton · Answer 2 · 2012-08-17T01:49:03.177

What you have is an inner table for each nested query. The pageblocktable can only iterate over a single table.

Follow the code sample below. Select your accounts into the Account list and then iterate over the list with one pageblocktable and two nested pageblocktables.

Controller

public List<Account> _accounts;
public list<Account> Accounts { 
    get { 
        if (_accounts == null) { 
            _accounts = [SELECT Name, (SELECT Name, Phone, Email, Last_Contacted__c, Contact.Membership_Type__c FROM Account.Contacts) , (SELECT Call_Disposition__c, StageName, CreatedDate, CloseDate FROM Account.Opportunities) FROM Account where Id=:UserInfo.getUserId()];
        }
        return _accounts;
    }
}

Visualforce

<apex:pageBlockTable value="{!Accounts}" var="acc" id="pageblocktable">

    <apex:column headerValue="Account Name" value="{!acc.Name}"/>  

    <apex:pageBlockTable value="{!acc.Opportunities}" var="ops" id="opportunities">
        <apex:column headerValue="Created Date" value="{!ops.CreatedDate}"/>        
        <apex:column headerValue="Call Disposition">
            <apex:inputField value="{!ops.Call_Disposition__c}"/>
        </apex:column>
        <apex:column headerValue="Sales Stage">
            <apex:inputField value="{!ops.Sales_Stage__c}"/>
        </apex:column>         
    </apex:pageBlockTable>
    <apex:pageBlockTable value="{!acc.Contacts}" var="con" id="contacts">
        <apex:column headerValue="First Name" value="{!con.FirstName}"/>  
        <apex:column headerValue="First Name" value="{!con.LastName}"/>
        <apex:column headerValue="Phone" value="{!con.Phone}"/>
        <apex:column headerValue="Account Name" value="{!con.Account.Name}"/>
        <apex:column headerValue="Email" value="{!con.Email}"/>
        <apex:column headerValue="Last Contacted" value="{!con.Last_Contacted__c}" />       
        <apex:column headerValue="Membership Type" value="{!con.Membership_Type__c}"/>
    </apex:pageBlockTable>

</apex:pageBlockTable>

Thanks a million Gerard!I will try it and reply if I still have issues. — Thys Andries Michels, Aug 17 '12 at 03:25
Hi Gerard for some reason it is just showing my Account Name fields and non of the others. Do you maybe know why? — Thys Andries Michels, Aug 17 '12 at 07:59

Apex SOQL subquery in Visualforce

2 Answers2