In my last post on relationships in Transfer, I talked about the ManyToOne relationship. I defined the Department object and associated employees with a department.
For today's post, I'll be talking about the ManyToMany relationship. This relationship exists when you have multiple connections between two objects. The example the Transfer docs give, and the I think that makes the best sample (especially considering where you are reading this), is the idea of blog entries and categories. A blog entry can have more than one category. On the database side, this is done via a join table with one column pointing to a blog entry and another column pointing to a category.
For the Employee Directory, I had a hard time thinking of a good way to add an example of a many to many relationship. I then thought of benefits. Typically everyone at a company gets the same set of benefits (dental, vision, medical, etc), but maybe at my fictitious company we let employees decide what benefits they want (perhaps each one takes a different amount of money out of your check). I began by creating a Benefit object:
<object name="benefit" table="benefits">
<id name="id" type="numeric" />
<property name="name" type="string" />
</object>
I kept the object rather simple. I could have added a 'deducation' field and a 'requirement' property as well, but since the important thing here is the relationship, I thought I'd keep it simple. I went ahead and added admin files for benefits. Like before though I'm not going to go into that as nothing has changed compared to the department files. (As a quick side note - now that I have multiple files in the Admin, I went ahead and quickly made a simple adminlayout custom tag. I defined the custom tag path in Application.cfc and wrote up a butt-ugly design for the admin. Stand back - I'm designing!)

When it comes to a ManyToMany relationship, the first choice you have to make is which object will define the relationship. In other words, should I define the relationship in Benefit or Employee? For me, it seems like Employee is the 'primary' or 'most important' object, so I figured that's where I should define the connection. You should note that you will not be able to go in both directions in Transfer. By that I mean, you know I'll be showing you soon how to get related data. Once we do that though we will only be able to get data related to that object, not the other way around. I can see me needing to get benefits for an employee. I can't see me needing to get employees for a benefit.
I added this to my Employee definition:
<manytomany name="benefits" table="employees_benefits">
<link to="employee" column="employeeidfk"/>
<link to="benefit" column="benefitidfk"/>
<collection type="array">
<order property="name" order="asc"/>
</collection>
</manytomany>
Let's step through this line by line. We begin with the manytomany tag. I give it a name and a table name. As with other declarations, the table name is optional if it matches the name.
Next we need to define the links. I have one link that that defines the connection to employee, and another one that links to the benefit. Note how I define both the Transfer name and the particular column.
The collection tag is interesting. As you can imagine, Transfer is going to make it easy to get all the related data. However, Transfer gives us a few options on how the data is returned. We can either get an array or a structure back. I preferred an array, and thats why I used type="array" in the tag. You can also have Transfer sort the result (which only makes in an array if you ask me).
Ok. So like before, I decided to manually set some data and play with my test file. Transfer provides a set of functions you can use with ManyToMany relationships (see full docs here). We can obviously get the items. We can add a related piece of data. We can remove a related piece of data. We can clear all the related data. Lastly, we can get a related piece of data at a particular index (if it exists). I began with a simple test of getting all the related data.
<cfset emp = application.transfer.get("employee", 1)>
<cfdump var="#emp.getMemento()#">
<cfdump var="#emp.getBenefitsArray()#">
The getMemento() method is just our debug function, and it correctly gets all the related benefits. The getBenefitsArray is a method automatically created by Transfer. This returns an array of TransferObjects, one for each related piece of data.

Alright - time to get cracking. I jumped back into my Employee edit form and began to add support for assigning benefits. I began by adding a line of code to get all the benefits. I'll use this for my drop down.
<cfset benefits = application.transfer.list("benefit", "name")>
Next I needed to create a default value for which benefits are selected. This was a bit more complex than the department example. Check out the code first and then I'll explain it:
<cfset benes = employee.getBenefitsArray()>
<cfif arrayLen(benes)>
<cfset oldbenelist = "">
<cfloop index="b" array="#benes#">
<cfset oldbenelist = listAppend(oldbenelist, b.getID())>
</cfloop>
<cfparam name="form.benefits" default="#oldbenelist#">
<cfelse>
<cfparam name="form.benefits" default="">
</cfif>
I began by getting all the related benefits for the employee. If any exist, I loop over each one and get the ID value. After the loop, I use that to param the form value. I could probably write that a bit simpler but it worked for me.
The code for the form is much like the department, except that now we have a multi-select:
<select name="benefits" multiple="multiple" size="#benefits.recordCount#">
<cfloop query="benefits">
<option value="#id#" <cfif listFind(form.benefits, id)>selected</cfif>>#name#</option>
</cfloop>
</select>
So finally, to save this change, I went with a simple nuke and reset policy:
<!--- nuke old ones --->
<cfset employee.clearBenefits()>
<cfloop index="b" list="#form.benefits#">
<cfset benefit = application.transfer.get("benefit", b)>
<cfset employee.addBenefits(benefit)>
</cfloop>
Relatively simple, don't you think? Oh, and get this. If you delete an employee, Transfer will take care of the related data for you. No need to write SQL to delete crap from the join table.
I went back into my test page and added a nicer formatted version:
<cfoutput>
#emp.getFirstName()# #emp.getLastName()# has the following benefits:<br/>
<ul>
<cfset benefits = emp.getBenefitsArray()>
<cfloop index="b" array="#benefits#">
<li>#b.getName()#</li>
</cfloop>
</ul>
</cfoutput>
Any questions? In the next post I'll circle back to ManyToOne and talk about that relationship versus OneToMany.
Archived Comments
"I can't see me needing to get employees for a benefit."
There is a case, my boss just called and it is budget cutting time. We need to see a list of who has each benefit to determine if it is worth keeping it.
Would you have to write a regular query at this point?
Brian, afaik, yes, or use TQL. But as you know, I'm also learning as well, so there is a chance I'm missing some feature.
Ray,
I've been going through your Transfer series of posts and they are excellent. I wanted to post one snag I ran into -- you mention this in your post:
"We begin with the manytomany tag. I give it a name and a table name. As with other declarations, the table name is optional if it matches the name."
I ran into an error when I didn't declare the table parameter. I got back a validation error saying that table was required per the transfer.xsd. I'm not sure if you would still get the error if you weren't linking to the xsd, but I thought I would mention it. I'm using Transfer 1.1.
Son of a (&$@, uh, Gun.
I was having fun and learning too and you went and snuck in a CFMX8'ism, that using an array as an attribute to a CFLOOP tag at line 24 of the Index.cfm file.
Now I have to stop and think how I'm going to convert the array back to a list, and have to include that code for each new download...
Still, thanks, this kind of progressive build-up of an application using Transfer is just what I needed and hadn't found till you started it. and as an aside, I'm trying to get Transfer under my belt to be able to have a hope of coming up to speed on ColdBox, my next learning curve to climb!
You don't have to convert it to a list. Change
<cfloop index="b" array="#benes#">
<cfset oldbenelist = listAppend(oldbenelist, b.getID())>
</cfloop>
to
<cfloop index="x" from="1" to="#arrayLen(benes)#">
<cfset b = benes[x]>
<cfset oldbenelist = listAppend(oldbenelist, b.getID())>
</cfloop>
ColdBox? Don't you mean Model-Glue? ;)
@Josh: Hmpth. The docs say it is optional. Let me ping Mr. Transfer.
OK, I over-reacted. Looked at the code and all I had to do to get it work under CFMX7 was re-write the loop:
<code>
<cfloop index="b" from="1" to="#arrayLen(benefits)#">
<li>#Benefits[b].getName()#</li>
</cfloop>
</code>
Looking at the docs,
http://docs.transfer-orm.co...
It doesn't say that it is optional, but it probably should be.
I'll log it as an enhancement.