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.

Download attached file.