Welcome to my third post on managing relationships with Transfer. If you want to get caught up on the earlier ones, or any of the other entries in this series on learning Transfer, please see the Related Blog Entries section below. Previously I talked about ManyToOne relationships (multiple employees were linked to single departments) and ManyToMany relationships (multiple employees linked up with multiple benefits). Today I'll cover the final variety - OneToMany.
I have to admit that I needed to read the docs on OneToMany a few times before it began to click in mind. The documentation talks about why you would choose a OneToMany or a ManyToOne. I don't think Mark Mandel will send a T800 after me if I quote directly from the docs:I think the last paragraph is what makes the distinction sink home for me. We never had a business need to go from benefits to employees. We only needed to get benefits for an employee. In a OneToMany, we may need to go both ways. So let's start by defining our business need. Employees will have a new type of data associated with them: Position. A position is simple a title (although I called it name), and a start and end date. So if you start at Microsoft as a Temp, that would be your first position (with associated dates). You could then get promoted to Vice President of Temps. This employee object then would have two positions related to him. Transfer will also let us go the other way with this relationship. We can look at a position and get the employee associated with it. I'll define my Position object like so:So, how does one decide whether to model a one-to-many in a database as a OneToMany or a ManyToOne?
First it is important to understand the difference between a OneToMany and ManyToOne in Transfer, quoting from another part of the wiki:
OneToMany composition is useful when you wish for TransferObjects on both sides of the relationship to see each other, or for the Parent to have a collection of the child objects attached to it.
A ManyToOne collection is useful when, either for application design, or performance reasons, you only want an Objects to load one side of the relationship, and not generate a collection of Objects.
1 <object name="position" table="positions">
2 <id name="id" type="numeric" />
3 <property name="name" type="string" />
4 <property name="startdate" type="date" />
5 <property name="enddate" type="date" />
6 </object>
By now I assume most folks are comfortable with the simple object definitions, so I won't go into it too much. Since positions are tied to employees and won't be edited separately, I did not update the admin with a list and edit set of files.
How do we associate positions with employees? As you can probably guess, we use a onetomany tag:
2 <id name="id" type="numeric" />
3 <property name="name" type="string" />
4 <property name="startdate" type="date" />
5 <property name="enddate" type="date" />
6 </object>
1 <onetomany name="positions">
2 <link to="position" column="employeeidfk" />
3 <collection type="array">
4 <order property="startdate" order="desc" />
5 </collection>
6 </onetomany>
Much like the manytoone tag I worked with two blog entries ago, I begin by giving it a name. This is how I'll reference the related data in the employee object. The link tag tells Transfer what object type to link to, and here is a point that may confuse you - the column here is the foreign key column in the other table (in our case, the positions table) that links back to the employee records. Again, like manytoone, we tell Transfer how to return the data - either in a struct or an array. I told Transfer to use an array and sort it by the start date, descending. This will give me the employees position with his latest position first.
So before I get into code at all, I opened up my database editor and added a few simple position records. For each one, I used an employeeidfk of 1. I then went back to my test script and wrote code to get and dump the first employee:
2 <link to="position" column="employeeidfk" />
3 <collection type="array">
4 <order property="startdate" order="desc" />
5 </collection>
6 </onetomany>
1 <cfset emp = application.transfer.get("employee", 1)>
2 <cfdump var="#emp.getMemento()#">
This results in:
2 <cfdump var="#emp.getMemento()#">
Sweet - there are my positions. The more formal way to get the positions would be very similar to ManyToMany method: getXArray, where X is the property.
1 <cfset emp = application.transfer.get("employee", 1)>
2 <cfdump var="#emp.getPositionsArray()#">
Alright - so how are we going to work with this data in the admin? I decided on a simple interface. When you edit an employee, you will see a list of his positions. For each we will list the name and the date range. I'll put a checkbox next to each to let you delete the position. Below this list I'll have a place to enter a new position. I could certainly allow for editing as well, but again, I wanted to keep it simple. Before I show the code, here is a quick screen shot:
2 <cfdump var="#emp.getPositionsArray()#">
Ok, so I began my modifications by getting all the positions for the employee:
1 <cfset positions = employee.getPositionsArray()>
I then created a table within my table for the form (ugly, I know, deal):
1 <tr valign="top">
2 <td>Positions:</td>
3 <td>
4 <table border="1">
5 <tr>
6 <th>Title</th>
7 <th>Dates</th>
8 <th>Delete</th>
9 </tr>
10 <cfloop index="p" array="#positions#">
11 <tr>
12 <td>#p.getName()#</td>
13 <td>#dateFormat(p.getStartDate())# to #dateFormat(p.getEndDate())#</td>
14 <td><input type="checkbox" name="delete_position" value="#p.getID()#" /></td>
15 </tr>
16 </cfloop>
17 <tr>
18 <td><input type="textbox" name="new_position_name" /></td>
19 <td><input type="textbox" name="new_position_startdate" /> to <input type="textbox" name="new_position_enddate" /></td>
20 <td> </td>
21 </tr>
22 </table>
23 </td>
24 </tr>
So theres a lot of HTML there, but in reality all we are doing is displaying the employee's positions. For each position, I created a checkbox named delete_position. Lastly, I added a row of blank fields for you to enter a new position.
The save side is a bit interesting. Let's look at how it works:
2 <td>Positions:</td>
3 <td>
4 <table border="1">
5 <tr>
6 <th>Title</th>
7 <th>Dates</th>
8 <th>Delete</th>
9 </tr>
10 <cfloop index="p" array="#positions#">
11 <tr>
12 <td>#p.getName()#</td>
13 <td>#dateFormat(p.getStartDate())# to #dateFormat(p.getEndDate())#</td>
14 <td><input type="checkbox" name="delete_position" value="#p.getID()#" /></td>
15 </tr>
16 </cfloop>
17 <tr>
18 <td><input type="textbox" name="new_position_name" /></td>
19 <td><input type="textbox" name="new_position_startdate" /> to <input type="textbox" name="new_position_enddate" /></td>
20 <td> </td>
21 </tr>
22 </table>
23 </td>
24 </tr>
1 <!--- handle positions --->
2 <!--- first, did we delete any? --->
3 <cfif structKeyExists(form, "delete_position") and len(form.delete_position)>
4 <cfloop index="p" list="#form.delete_position#">
5 <cfset pos = application.transfer.get("position", p)>
6 <cfset application.transfer.delete(pos)>
7 </cfloop>
8 </cfif>
9
10 <!--- did we add a new one? --->
11 <cfif len(form.new_position_name) and len(form.new_position_startdate)
12 and isDate(form.new_position_startdate) and len(form.new_position_enddate)
13 and isDate(form.new_position_enddate)>
14
15 <cfset position = application.transfer.new("position")>
16 <cfset position.setName(form.new_position_name)>
17 <cfset position.setStartDate(form.new_position_startdate)>
18 <cfset position.setEndDate(form.new_position_enddate)>
19
20 <cfset position.setParentEmployee(employee)>
21 <cfset application.transfer.save(position)>
22 </cfif>
Deleting isn't too fancy. I just check to see if any of the checkboxes were selected and if so, I call the delete() method on the objects.
For adding positions, I checked to see if all the values were filled out for the position. If you remember, I said earlier that I wasn't doing a lot of validation. In this case I check for valid values, but don't bother recording any kind of error otherwise. I create a new position TransferObject and set the values. The only new thing here is: setParentEmployee. This links the position to the employee. Once I save the position, the conneciton is done.
Easy, right? One small nit. If you call this code on an employee object that isn't saved, you will get an error. I simply moved this line above it:
2 <!--- first, did we delete any? --->
3 <cfif structKeyExists(form, "delete_position") and len(form.delete_position)>
4 <cfloop index="p" list="#form.delete_position#">
5 <cfset pos = application.transfer.get("position", p)>
6 <cfset application.transfer.delete(pos)>
7 </cfloop>
8 </cfif>
9
10 <!--- did we add a new one? --->
11 <cfif len(form.new_position_name) and len(form.new_position_startdate)
12 and isDate(form.new_position_startdate) and len(form.new_position_enddate)
13 and isDate(form.new_position_enddate)>
14
15 <cfset position = application.transfer.new("position")>
16 <cfset position.setName(form.new_position_name)>
17 <cfset position.setStartDate(form.new_position_startdate)>
18 <cfset position.setEndDate(form.new_position_enddate)>
19
20 <cfset position.setParentEmployee(employee)>
21 <cfset application.transfer.save(position)>
22 </cfif>
1 <cfset application.transfer.save(employee)>
So really, thats all there is too it. There are additional methods you can do with this type of relationship that I didn't need for my application. Let's take a quick look at them:
- If you want to remove the connection from the child object and not delete it, you can call object.removeParentX, where X is the name of the parent object type. I can't see why you would do this. If you do, you end up with a child that has a null foreign key. But if you want to - you can.
- A child object, like a position, can see if it has a parent using ob.getParentX.
- A child object can get the parent using ob.getParentX.
- A parent can get a child object at a particular position. So given that we sorted our positions by start date descending, you could get the most resent position by doing ob.getPosition(1).