Dynamically change display orders of data in ColdFusion

Question

Using ColdFusion, I'm trying to setup a menu and menu items application where you can change the order of the menu and also the menu items within the menu. I've found something relative, but it's not completely helping, http://wil-linssen.com/musings/entry/extending-the-jquery-sortable-with-ajax-mysql.

So if you view the demo there, that is essentially what I'd like to have, although with the sub items below each main item.

I guess what I'm having a hard time understanding is what to use in the database for a displayOrder column and then what the update would look like.

This is basically what I need to be able to do:

Basketball (category 1)

• Shoes

• Shirts

• Socks

Baseball (category 2)

• Bats

• Balls

• hats

I need to be able to change the order to whatever I wanted for both categories and their items so it could be:

Baseball (category 1)

• Bats

• Hats

• Balls

Basketball (category 2)

• Shirts

• Socks

• Shoes

I think the thing throwing me off is what's going in the displayOrder column of the database. I'm assuming their id, but then how would you correctly order by in the SQL?

Answer 1

Following the example you would need a column in your database to store the order of items you want to show. Using a numeric value helps in ordering when you are querying the items in order. An example might be:

SELECT ProductCategories FROM MyTable ORDER BY DisplayOrder

Answer 2

Another way is to serialize the array in JSON / WDDX and store in a varchar field in the DB. That way, the order is preserved and you can deserialize it back to an array in the correct order easily.

Answer 3

The simplest way I know of to do this is to create a parent relationship in the database. You will have each sub-item point to the parent item in the same table. To get the parent items, select * from the table ordered by your sort column where parent is null. Then, loop over these, getting items whose parent is the current top-level item.

You can find more information about this if you google/bing/whatever for variations on "menu tree" for specifically what you want, or look for how to build trees in a DB for more generic information.

Answer 4

Since it is a menu, you might also look into using the nested set model. It is not as straight-forward as the as parent/child model (conceptually). But it makes manipulating a moderately sized hierarchy (like a menu) much easier IMO.

I am not sure which database you are using, but you can find a good comparison of the two models (adjacency and nested sets) on the MySQL site

There is also, at least one cfc for managing nested sets at riaforge.org

Answer 5

No one really solved it, but I ended up getting it to work right by parsing the sortable serialized array and looping through it. Then I determined whether or not it was a category or an item.

The demo can be found here: http://develop.michaelpstone.net/development/demos/treeView/stone/secure/agentsadmin.cfm I'll be supplying a more in depth look within the cffunction in a blog post coming later.