+ Start a Discussion
src0010src0010 

Group two objects on VisualForce page using SOQL Query w/ Left Outer Join

Currently I have a working page which lists items from one object.  I am trying to update this to group by two objects.  Details below:

 

 

Current APEX class code:

 

public class projectTracking
{
  private List<Project_Billing_Item__c> projects;
  public List<Project_Billing_Item__c> getProjects()

    {
      projects = [ SELECT Project__c, Name, Employee__c, Description_of_Work__c,   Hours_Worked__c, Date__c 
						FROM Project_Billing_Item__c 
							WHERE Employee__c = :Userinfo.getUserId()  
            ];
			
      return projects;
    }
}


Current VisualFoce code:

 

<apex:page controller="projectTracking" showHeader="true" renderAs="html">

<apex:sectionHeader title="My Projects" subtitle="{!$User.FirstName} {!$User.LastName}"/>
<p/>

<apex:form >
<apex:pageBlock title="List of current billing items" id="pageBlock">
<apex:pageMessages ></apex:pageMessages>
<apex:pageBlockTable value="{!projects}" var="proj" rendered="{!NOT(ISNULL(projects))}">
<apex:column value="{!proj.Project__c}"></apex:column>
<apex:column value="{!proj.Name}"></apex:column>
<apex:column value="{!proj.Description_of_Work__c}"></apex:column>
<apex:column value="{!proj.Hours_Worked__c}"></apex:column>
<apex:column value="{!proj.Date__c}"></apex:column>
</apex:pageBlockTable>
</apex:pageBlock>
</apex:form>

</apex:page>

 

Produces the following rendered VisualForce page:

 

VisualForcePage

 

 

What I am trying to do now is group all the Project Billing Items by Project on the VisualForce page.  Currently I have updated the APEX class with the correct SOQL Query w/ Left Outer Join to group the results accordingly.  APEX class code below:

 

 

 

public class projectTracking
{
  private List<Project_Billing_Item__c> projects;
  public List<Project_Billing_Item__c> getProjects()

    {
      projects = [ SELECT Name, 
					(SELECT Project__c, Name, Employee__c, Description_of_Work__c, Hours_Worked__c, Date__c 
							FROM Project_Billing_Items__r 
									WHERE Employee__c = :Userinfo.getUserId()) 
					FROM Project__c
            ];
			
      return projects;
    }
}

 

I am not sure where to start with the VisualForce code though.  How do I update that to reference the List which is referencing two custom objects?   Thanks for the help and let me know if I can provide any additional information!

 

Ron HessRon Hess

 

if you want to use a page block table, you may need to create a flattened inner class that will hold the data with the repeated items ( project id) in it so that you can use a simple page block table.

 

other wise you can build a table with two nested loops, one for all projects, and an inner loop for each item in a project.

This method may make it hard to use pageblock table

 

ansonteoansonteo

Do you have sample coding regarding on how to access the Left outer Join in Visual Force for custom objects?

Dhruv Khattar 8Dhruv Khattar 8
If it helps, you can use nested <apex:repeat> to achieve this.
For instance, if you have a Class__c object with related Student__c objects, and the relationship is Students__r, it would be
<apex:repeat value="{!ListOfClasses}" var="c">
     Class Name: {!c.Name}
          <apex:repeat value="{!c.Students__r" var="s">
               Student name: {!s.Name}
          </apex:repeat>
​</apex:repeat>