manytoone and a onetomany in Transfer ORM

There is a page on the Transfer ORM docs entitled "Can I use the same foreign key for a manytoone and a onetomany?". The short answer is no, but sometimes you need to be able to get the data, so how can you do it?

Here is a fairly typically Transfer config file. We have defined two objects in the member package, User & Role. Many Users can be composed of the same Role (i.e. you might have multiple users with the same role), so we use a manytoone relationship between User and Role.

<package name="member">
  <object name="User">
    <id name="UserID" type="numeric" />
    <property name="Username" type="string" />
    <property name="Password" type="string" />
    
    <!-- compositions -->
    <manytoone name="Role">
      <link to="member.Role" column="role_id" />
    </manytoone>
  </object>
  
  <object name="Role" decorator="com.model.member.Role">
    <id name="RoleID" type="numeric" />
    <property name="Name" type="string" />
  </object>
</package>

This works really well, we can get a User and then get the role for that user by doing something like this:

<cfset User = getTransfer().get( "member.User", 1 ) />
<cfoutput>
User : #User.getUsername()#<br />
Role : #User.getRole().getName()#<br />
</cfoutput>

So what happens when we want to get all the Users who belong to a Role?

Your first thought might be to define a onetomany relationship on your Role object that links to User. Sorry, but that's not going to work as Transfer will get horribly confused and you may end up with infinite loops and a "Stack Overflow" error.

OK, so how about adding a RoleID property to our user object which maps to the "role_id" foreign key in our database User table? Nope, a property can't map to the same column that your using for the manytoone relationship. This is because when you want to save your object, Transfer won't know which value to insert into the "role_id" database field.

One more attempt, hopefully it will be third time lucky!

OK, in the Role decorator (you are using decorators aren't you!), you can set up a new method called something like getUserList

<cffunction name="getUserList"
  access="public" 
  output="false" 
  returntype="query" 
  hint="I return a query of Users for this Role.">
  
  <cfset var result = "" />
  
  <cfimport prefix="t" taglib="/transfer/tags">
  
  <t:query name="result" transfer="#getTransfer()#">
    FROM member.Role
      JOIN member.User
    WHERE member.Role.RoleID = <t:queryparam value="#getRoleId()#" type="numeric">
  </t:query>

  <cfreturn result />
</cffunction>

Now we can get a Role and return a list of users:

<cfset Role = getTransfer().get( "member.Role", 1 ) />
<cfoutput>
User : #Role.getName()#<br />
<cfdump var="#Role.getUserList()#" />
</cfoutput>

As a side note, if you do find yourself creating onetomany relationships in Transfer, think carefully about whether you need the advantages of getting an array of objects or if you simply want a query, in which case you should consider adding a method to your decorator.

Thanks to Bob Silverberg and Stephen Moretti for their input.

• Posted in:
• ColdFusion
• Transfer

No comments