ColdFusion 9 ORM and relationships

Dec 30

Thanks to the ORM discussion over on Brian Kotek’s blog (original, follow-up), I’ve been doing some extensive CF9 ORM debugging and logging.

Count of Related

When trying to fetch a count of related items, such as Ray Camden and Jon Hartmann have shown, and It turns out that the laziness of the relationship comes into play.

Both Ray and Jon assert that the naive way to get a count is inefficient:

artistCount = arrayLen(album.getArtists())

This is true … most of the time. For a relationship with lazy="false" or lazy="true", this naive code fetches all Artists, not just a count. This example assumes a many-to-many relationship with a link table named album_artists:

SELECT link.album_id, link.artist_id, artists.id
FROM album_artists AS link
    LEFT OUTER JOIN artists
        ON (link.artist_id = artists.id)
WHERE (link.album_id = ?)

But, if the relationship is defined with lazy="extra", the query is optimized into:

SELECT COUNT(artist_id)
FROM album_artists
WHERE (album_id = ?)

Of course, this isn’t magic—the ORM isn’t really noticing that you only want the count. It’s just checking to see how many there are so it can make an array with the correct number of placeholder objects, which won’t be populated until you actually need them.

Still, it’s certainly an argument for adding lazy="extra" where possible. According to the lazy loading documentation, you can use it for one-to-many and many-to-many relationships.

Inverse Relationships

Here I must apologize to several people, including Brian Kotek and Rupesh Kumar. In my work with ORM, I’d been playing around but hadn’t built anything really big with it. In doing that, I had formed some opinions that worked in my little test documents, but were really just edge cases and bad tests. I spouted some assertions that just didn’t hold up. Sorry, guys.

Given my example case above, with artists and albums having a many-to-many relationship, I had thought that if you did not specify inverse="true" for the relationship, but instead defined the relationship fully in each direction, that the ORM would automatically mirror any assignments to either side of the relationship.

This assertion was based on the fact that it sometimes appears to work, depending on how your code is set up:

laundry = entityLoad("Album", { Title = "Laundry Service" }, true);
cover = entityNew("CoverImage");
cover.setFileName("laundry.jpg");
cover.setAlbum(laundry);
entitySave(cover);
writeDump(laundry.getCoverImages());

This code works, but only in a sort of quantum-flux-Shroedinger’s-cat sort of way. If your objects are set up with extra lazy relationships, and you have a fresh ORM session, and none of the objects have been loaded before … it looks like the single setAlbum statement causes not only the cover object to see the laundry object, but the other way around at the same time. That is, it appears to have done this, too:

laundry.addCover(cover);

But, it’s a damnable lie. It only looks that way because of the lazy loading.

When we first load the laundry object, the extra lazy loading tells the ORM to not bother looking at the cover images until someone wants them. The writeDump statement enumerates the cover images when it is inspecting the array returned from getCoverImages, so that is the first time the ORM has to load the covers—after we’ve saved the new cover image. We’d see a completely different result if we had done anything with the laundry object’s cover images before we saved the cover entity.

Long story short, as goofy as it may seem, you really do have to use inverse="true" and double-set your relationships:

cover.setAlbum(laundry);
laundry.addCover(cover);

With inverse="true" on one side, one of those function calls is reduced to a no-op.

That double-setting seems dumb to me and just begs for someone to screw it up. It seems to me like the ORM should be able to notice that the relationship is two-way and should magically mirror the assignment for you. That is, if you called cover.setAlbum it should implicitly call album.addCover for you, and vice-versa. Having to resort to goofy hacks to avoid the performance implications of double-setting when you are explicitly double-setting seems … well, hackish.

Logging ORM Queries

Rupesh has a great post on how to log CF9 ORM SQL, but there’s one slight gotcha that I’ve run into: it doesn’t mix perfectly well with <cflog> and writeLog. There’s obviously some sort of race-condition buffering thing going on.

I had thought it would be rather clever to use writeLog statements to the same hibernatesql.log file that the ORM writes its query debugging information to. This worked … for a while. Eventually, only the writeLog statements showed up, as if the ORM could no longer write to the file.

Also, when the ORM and writeLog were both writing to the file, one would occasionally get ahead of the other. If I had a writeLog statement followed by an entityLoad statement, the SQL might come before the log message, or vice-versa. They would both have the same timestamp (as the resolution is only a whole second), but clearly you can’t entirely trust the exact order of the log lines when doing something like this.

Long story short, you probably shouldn’t use the CF logging tags and functions to write to the same log file the ORM is using.