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.

Solution

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:

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!

Comments

[Thys Andries Michels]

Thanks a million Gerard!I will try it and reply if I still have issues.

[Thys Andries Michels]

Hi Gerard for some reason it is just showing my Account Name fields and non of the others. Do you maybe know why?

[metadaddy]

You're welcome... If it works for you, please upvote and mark it as the answer.

[Thys Andries Michels]

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?

[metadaddy]

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.

[Gerard Sexton]

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!

[Thys Andries Michels]

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.

[metadaddy]

@ThysAndriesMichels - I don't think you can use a standard set controller - see my edited answer.

[Thys Andries Michels]

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?

[metadaddy]

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?

[Thys Andries Michels]

How will I keep track of those two fields maybe have an onchange action and put the values in a Map<Call_Disposition__c, Map<Id, Opportunity>>?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?

[metadaddy]

Those two fields seem to be per-Opportunity, so maybe link to Opportunity from each OpportunityContactRole row?

[Thys Andries Michels]

I settled for a work around. Will post my result when I am done. Thanks for all your help Pat!