Forum OpenACS Q&A: sort_order / sort_key - Moving objects up and down a list

I was re-reading an old thread, https://openacs.org/forums/message-view?message_id=75763 which discusses object-ids versus "pretty-ids" in URLs, where Andrew Piskorski just asked for a conclusion on that topic (way to go, Andrew) when a similar issue came to my mind ...

Some packages (WP, Survey, ETP etc) lets you change the internal order of items (moving them up/down) by manipulating the sort_order.

Has anyone figured out which implementation is the best?

This is perhaps another issue where we would like to settle with a "canonical solution"?

ETP manipulates the tree_sortkey, which is a bad idea. Its dependent on the postgresql tree_sortkey implementation.

Survey has an explicit sort_order column in the survey_questions table.

Hi Ola,

I used the tree_sortkey to explicitly sort on my current CMS.  Due to the fact that I was in a tight sched way back then.  I then brought it up on the CMS forums (where is that now?).  We need to really have this machanism in CR.  Either the sortkey (which OpenForce I think also uses in Oracle .LRN) or some other method.  One method is the relations in CR, but then that does not give me a good way to make sorting heirarchy as tree_sortkey.  Or it can, but I think maybe more difficult.

Anyway I hope we can make a final decision on how to explicitly sort items even on just in CR.  I still have to investigate what Dave has brought up, cr_child_rels.  On my first try on cr_item_rels order_n did not prove to be easy.  So I jumped back to use tree_sortkey.  There are 2 major drawback to tree_sortkey.

#1. Its still pg only.
#2. Manipulating will put an entire branch into a bad state, although I manipulated it in a transaction.  It is repairable just in case it really goes bad, but then tree_sortkey was not meant to be manipulated.

Dave, yes I'm using a sort_key column in my lookup table.

I've only tested the below script on Oracle and it doesn't seem to work properly. I have not installed survey and tried there. (I *think* "case" should work as good as "decode" (or better since it's standards compliant) in Oracle 8.1.7, or am I wrong?)

element-swap.tcl (borrowed from survey and shortened for brevity):

ad_page_contract {
    
    Swap two curriculum elements.
  
} {
    element_id:integer,notnull
    sort_order:integer,notnull
    direction:notnull
}

if { [string equal "up" $direction] } {
    set next_sort_order [expr $sort_order - 1]
} else {
    set next_sort_order [expr $sort_order + 1]
}

db_dml swap_sort_orders {
	update cu_elements
	set    sort_key = (case when sort_key = :sort_order then :next_sort_order
                              when sort_key = :next_sort_order then :sort_order end)
	where  element_id = :element_id
	and    sort_key in (:sort_order, :next_sort_order)
}

This would assume that there are no gaps in the sequence of sort_keys, which may be hard to guarantee.

I've also seen solutions (by OF, I think, but I don't remember what package it was right now) that involve two or three separate updates in a transaction. That may be safer.