ColdFusion ORM: cascade=all-delete-orphan behavior differs depending on relationship type

Thursday, February 10, 2011

Long title, I know. Short post.

Executive Summary

I thought that you needed cascade=”all-delete-orphan” any time you wanted to ensure an element in a one-to-many property was removed from the join table. For example, blog.removeComment( comment ) should remove the row from BlogComments. Turns out, it’s not always required.

I still think you should use it all the time, however.

This is one of those “I learned something new but it doesn’t change anything” type of posts.

all-delete-orphan and LinkTable

When removing elements from a one-to-many collection, you typically specify cascade=”all-delete-orphan” on that property. Then, when you remove the item and re-save the entity, it’ll remove that item from the join table. Here’s an example, using a typical “linktable”, which you use when you have a simple 2-key join table. Given an entity named “Event.cfc”, with a property named “attendees” which maps to an Attendee.cfc:

property name="attendees" fieldtype="one-to-many" cfc="Attendee"
        linktable="J_Events_Attendees"
        fkcolumn="EventID"
        inversejoincolumn="AttendeeID"
        singularname="attendee"
        cascade="all-delete-orphan";

With a relationship like this, if you call event.removeAttendee( someAttendee ), then ORM will remove the link from that linktable for you, thanks to the cascade.

Here’s what I learned… cascade=”all-delete-orphan” doesn’t matter in the case of a simple linktable. If you set it to cascade=”all”, it’ll do exactly what you want.

all-delete-orphan and intermediary entity

BUT: it absolutely matters when you have a property that is not a simple link table but is instead an entity in itself, representing a link in addition to other data.

Consider this… an Event entity mapping to an “EventComment” entity. EventComment comprises more than a simple “eventid,commentid” relationship; instead, it has a comment string, and also an Attendee and a date and who knows what else. These “joins with additional data” are more common than simple joins in my experience, if for no other reason than everyone seems to *need* CreatedBy, CreatedOn, ModifiedBy, and ModifiedOn fields even though you never use them.

That mapping looks like:

property name="eventComments" fieldtype="one-to-many" cfc="EventComment"
        fkcolumn="EventID"
        singularname="eventComment"
        inverse="true"
        cascade="all-delete-orphan";

 

Now check this out. If you call event.removeEventComment( someComment ), it’ll remove it from EventComment’s table as expected. But unlike the earlier example, if you change it to cascade=”all”, it will not behave the same way. In fact, the behavior I observed, using MySQL, is this:

  1. The element is removed from Event’s array of EventComments
  2. refetching the object in the same request (i.e. entityLoadByPK( “Event”, event.getId() ); returns an object without that EventComment… even if you have FlushAtRequestEnd=false, and AutomanageSession=false, and perform the entitySave() in a transaction (suck it, Hibernate Session Object)
  3. The EventComment is not in fact deleted. If you refetch in another request or check it out in the query browser, it’s still there.

Bottom line: no deletion, though it *looks* like it’s deleted. These ORM headfakes’ll drive a dude to drink (More).

This is perhaps completely obvious that it should behave this way. I so rarely work with 2-column join tables, and while futzing about tonight with some examples I stumbled onto this difference in behavior accidentally.

My Takeaway

You don’t need all-delete-orphan when working with simply a linktable. However, I strongly suggest using it, since it doesn’t cause damage and more clearly expresses intent. And in the event that your simple join turns into a multi-column affair requiring an intermediary entity, you save yourself one fewer “now why in the hell isn’t this deleting anymore?” moment.

--Ommmmmm….

--Marc

6 comments:

Kerr said...

Having stepped away from this piece of development for awhile, I'm glad you posted this observation. I am in the midst of teaching one of my colleagues CF ORM, and I will definitely add this to my "WTH just happened?" list.

"suck it, Hibernate Session Object" -- Hilarious!

Marc Esher said...

Thanks Kerr.

Just to be clear, for posterity's sake: I don't think this is a bug, and as Bob Silverberg pointed out to me, using a linktable for one-to-many relationships is quite rare, bordering on perhaps stupid, and my example with linktable is in fact a many-to-many

David said...

Hi Marc,

Great post! I'm new to ORM, and oddly enough, working on an event management system myself!

The one thing I can't seem to wrap my head around though is how to set up the intermediary entity. I've seen all sorts of samples about a one-to-many and many-to-one, showing bi-directional methods, and setting inverse and all. But I've yet to find a good working example of say something like Person > PersonEmail (with all my audit stuff i'll never use anyways! :) ) >Email. I have items like useType in my join table. I also want to use the same Email table to join up with my Organization entity and do the same thing with Org > OrgEmail > Email.

Any thoughts?

Thanks,
David

Marc Esher said...

Hey David, the intermediary entity will just be another entity with many-to-one relationships. So the PersonEmail would look something like:

component....{
property name="person" fieldtype="many-to-one" cfc="Person" fkcolumn="personId";

property name="email" fieldtype="many-to-one" cfc="Email" fkcolumn="emailId";

property name="whatever"...;

Here's an example... check out the "EventComment" CFC: https://github.com/mxunit/mxunit-cfmeetups/tree/master/CFObjective_ORMZen/code/h_one_to_many_deepdive_synthesis

Marc Esher said...

Let's try that link again: http://cfml.us/ym

David said...

Hi Marc,

Thanks for the reply and link! I've downloaded all the files from your presos, and am going through it now.