Search

Monday, July 23, 2007

Using Reactor to Execute a Join Query

Let's say that you want to create a list of users with associated roles. You have a Users table and a Roles table defined in your database. The link between the two is the RoleID in the Users table that maps to the ID column in the Roles table. Your reactor.xml should have the following to defined the relationship between the Users and the Roles tables:
<object name="Users">
 <hasOne name="Roles">
  <relate from="RoleID" to="ID" />
 </hasOne>
</object>
Inside the users gateway object (UsersGatewaymssql.cfc for the MS SQL Server version) instead of doing this:
<cffunction name="GetUsersList" access="public" output="false" returntype="query">
 <cfset var usersList = 0 />

 <!--- Get a list of users from the database with their associated roles --->
 <cfquery name="usersList" datasource="#_getConfig().getDsn()#">
 select  u.BadgeNumber, r.RoleID, r.Name
 from  dbo.Users as u
    inner join
    dbo.Roles as r
    on
    u.RoleID = r.ID
 </cfquery>

 <cfreturn usersList />
</cffunction>
You define the function as:
<cffunction name="GetUsersList" access="public" output="false" returntype="query">
 <cfset var usersList = 0 />

 <!--- Create a reactor query --->
 <cfset query = this.createquery() />

 <!--- Join the users and the roles tables --->
 <cfset query.join('Users', 'Roles', 'Roles') />

 <!--- Execute the query --->
 <cfset usersList = this.getbyquery(query) />

 <cfreturn usersList />
</cffunction>
// //]]>