Andrew's ramblings

ColdFusion and ColdBox creating and using Nested Sets with ORM

Tags: ColdFusion 9, CFORM, Coldbox

I was curious on how to go about doing something like this in ColdFusion ORM, and how hard it would be to accomplish. The problem wasn't as hard as I thought it might be, in fact once you get around the concept of how Nested Sets work. It was fairly painless putting it together.

The first hurdle was setting up the Entity to be correct for our needs, and I ended up creating the following entity.

The first thing you might notice that I am doing here, is that I am extending the component with the BaseORMService from ColdBox. This is because later I am going to use some of its features, to make my life a little easier.

Now there are 3 points of interest in this code that really need to be explained, the first is the parent property where I am doing a many to one back on itself. This is not really important that you do it this way, you can very easily just use a normal numeric property on the entity, it is just that I would like to get the object with one line of code rather than two later.

The next 2 properties are left and right, which will be used to hold the left and right of this category.

I won't go into the workings of this, as it is fairly complicated to explain. However if you would like to read more and understand this, then just head on over to http://dev.mysql.com/tech-resources/articles/hierarchical-data.html.

Ok that is pretty much it as for the definition of the Entity, now comes the magical part.

The first bit of magic will be an ORMEvent that I will use called preInsert(), which will do a lot of work for us and the code will begin to look like this.

So far this is the beginning of what we are going to do, and I just want to explain a few things before giving away to much.

The very first thing we are doing here is getting a copy of the current session, so that we can continue along our way with whatever session management you have in place already.

The next thing I am doing is getting a copy of the entity name, this is not required and you'll see later why. But I did this because I actually could place this into a new file and extend it to reuse the code later, I haven't fully refactored this code as yet and is pretty much as it is since I got it happening. But if you aren't going to do that, or find yourself in a position where you'll ever only need this for one entity, then its up to you how you approach it.

And last but not least we then set the left and right properties of the entity we are trying to save, this will be explained how we calculate this a bit further on.

Now comes the interesting bit, we are going to see if the Entity object we are trying to save, actually has a parent or not. Because we need to do different things if it has or not.

So lets work on the fact that the Entity has no parent, and place this code in that block of code. I should also point out to people, that the executeQuery() is a method in the BaseORMService that we extended with earlier.

This is very simple and it gets the last used right, so if you followed the link explaining Nested Sets. You know the outer most parent will have the highest right, so you can then adjust the current Entity to use the next right on the left of this entity. That is pretty much it for saving an entity with no parent.

The next bit of magic will the come when the Entity does have a parent, and this will be handled with the following code.

As you can see this is a little more complicated, so lets take a loot at it step by step.

The first thing we do is get the right value of the parent, we then go and update the left value of all data where the left is greater than then right of the parent. We then do the same with the right values, always increasing the left and right by the value of 2.

Now that takes care of all the adding of Categories in this example, but what if we want to delete a category from the database. And to overcome this we will need to use a method for deleting, which is going to look something like this.

You can also see we are doing a bit of work in here as well, and basically it deletes the children and itself in the first HQL listed. But once we have done that we need to then adjust the left and right values, this enables that nested set is kept up to date when this occurs.

That's the hard part and at the same time, it becomes the easy part. The reason being is that by doing it here like this, we take all the extra work of having to lock, and manage the updates and rolling back etc., as it is all handled in the events.

And now that this work is out of the way, how do we go about using it.

This will basically create a Nested Set that has a top hierarchy called Furnishings and Electronics, and then create two sub categories for Electronics called Televisions and Computers, which computers will contain two more sub categories called desktops and laptops.

And all the maintenance of keeping the Nested Set up to date is taken care off.

Ok so we are able to create the categories, but how would we go about using them. Now please be aware that there are two ways to get this, and it will always depend on what your needs are but lets say I am wanting to mimic say All top parents, and all there children one level deep. If this doesn't make sense to you then phpBB uses this in their forum application, the first level is just the top categories with one level deep. And when you select a forum it then goes deeper by then getting a list to all children.

This example would mimic the way phpBB does the first screen on their forums.

I should also point out that as the delete is not an event, and it would have been so nice that ColdFusion had the postFlush() event. Then it would have made this so much smoother, but as the delete is a method then you will need to do this in your code.

Which isn't that much of a big deal, but it means that you can not use any other delete method, and you are forced to do it this way.

Well that's Nested Sets with ColdFusion ORM and ColdBox, hope you enjoyed this.

TweetBacks

ColdFusion and ColdBox creating and using Nested Sets with ORM - http://goo.gl/Afi7e Apr 9, 2011