Traversing Multiple ORM Associations with ColdBox CriteriaBuilder


Let’s say you have the following entities:

  • Report
  • Confirmation
  • Staff
  • Team

And the following associations between the entities:

  • A report can have one to many confirmations submitted by staff
  • A confirmation belongs to a staff
  • A staff belongs to a team

And let’s say you are querying the Report entity but also want to show Confirmation, Staff and Team entity details in a ColdBox view. How would you do use ORM associations and ColdBox’s CriteriaBuilder to navigate multiple associations and bring back the required information?

Here’s how we can achieve that goal by using the ColdBox CriteriaBuilder’s CreateAlias method:

  • Create a link from Report to Confirmation with CreateAlias
  • Create a second link from Confirmation to Staff with CreateAlias
  • Lastly, create a third link from Staff to Team with CreateAlias
  • Link all of the above together by chaining the CreateAlias, i.e. CreateAlias().CreateAlias().CreateAlias()

Here are some example code snippets to illustrate:

1. Entities and their Associations

Report Entity


component persistent="true" extends="<path-to-base-entity>" table="reports" { 
 property name="report_id" fieldtype="id" generator="identity" setter="false";
 property name="startDate" ormtype="date";
 property name="endDate" ormtype="date";

 property name="confirmations" fieldtype="one-to-many" inverse="true" cfc="<path-to-confirmation-model>" singularname="confirmation" fkcolumn=report_id" cascade="delete"; 
 
 public Report function init(){      
        return this;
 }
} //component

Confirmation Entity


component persistent="true" extends="<path-to-base-entity>" table="confirmations" { 
 property name="confirmation_id" fieldtype="id" generator="identity" setter="false";
 property name="comments" sqltype="nvarchar" length="800";

 property name="staffs" singularname="staff" fieldtype="many-to-one" cfc="<path-to-user-model>" fkcolumn="staff_id";
 
 public Confirmation function init(){
    staffs = [];    
    return this;
 }
} //component

Staff Entity


component persistent="true" extends="<path-to-base-entity-model> " table="staffs" {
  property name="staff_id"  fieldtype="id" generator="identity" setter="false";
  property name="firstname" sqltype="nvarchar" length="100";
  property name="lastname" sqltype="nvarchar" length="100";

  property name="teams" singularname="team" fieldtype="many-to-one" cfc="<path-to-team-model>" fkcolumn="team_id" foreignkeyname="FK_staff_team_id_teams";

  public User function init(){
    teams = [];
    return this;
  }
} //component

Team Entity


component persistent="true" extends="<path-to-base-entity-model>" table="teams" {
  property name="team_id" fieldtype="id" generator="identity" setter="false";
  property name="teamName" sqltype="nvarchar" length="50";

  public Team function init(){
    return this;
  }
} //component

2. Querying with CriteriaBuilder and CreateAlias

In your Report entity handler Report.cfc, maybe you have an action that retrieve the information for the Report entity listing view. Let’s say this action is called list. Here’s the code to retrieve the information from the Report, Confirmation, User and Team entities:


/* Report.cfc */
component accessors="true" {
   property name="reportService"    inject="model:ReportService";
   property name="sessionStorage"   inject="coldbox:plugin:SessionStorage";

   function preHandler(event,action){
        
   }
   public void function list(event){
      var rc = event.getCollection();
      var cr = reportService.newCriteria();

      cr.createAlias('confirmations','c').createAlias('c.users','u').createAlias('u.teams','t');
      rc.reports = cr.list(asQuery=false);

   } //list

}//component 

Note:

  • We are chaining the CreateAlias method to link the entities via their associations, so we can traverse the entities and display the required information.

3. Displaying the Information

Ok, we should now be able to retrieve all the information we needed using ColdBox CriteriaBuilder. Let’s see how we can access and display them. And let’s say we want to display the following information:

  • Report ID
  • Report Start Date
  • Staff First and Last name
  • Team Name
  • Confirmation comments

<cfoutput>
  <table>
    <thead>
      <tr align="left">
        <th>ID</th>
        <th>Start Date</th>
        <th>Staff Name</th>
        <th>Team</th>
        <th>Comments</th>
      </tr>
    </thead>
    <tbody>
      <cfloop array="#rc.reports#" index="report">
        <tr>
          <td>#report.getReport_id()#</td>
          <td>#DateFormat(report.getStartDate(),"dd/mm/yyyy")#</td>
          <td>#report.getStaffs().getFirstname()# #report.getStaffs().getLastname()#</td>
          <td>#report.getTeams().getTeamName()#</td>
          <td>#report.getConfirmations().getComments()#</td>
        </tr>
      </cfloop>
    </tbody>
  </table>
</cfoutput>

Notes

  • Please note the getters and setters are automatically created.
  • Staffs, Teams and Confirmations are ORM associations, and hence their respective getters are:
    • getStaffs()
    • getTeams()
    • getConfirmations()
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s