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"?

Ola,

Using the content repository, you can use cr_child_rels to link items to a folder, or another item and use the order_n column to store the sort order.

There are also cr_item_rels to relate cr_items to objects outside the CR and that also has an order_n column.

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.

Dave,

Interesting comments indeed.

What I am mostly thinking about is the API and UI for manipulating this stuff, but the back-end is most certainly worth discussing too. If you or anyone else have compared the implementations and chosen one over the other, I'd be interested to know your reasoning behind the decision.

For my own purposes (curriculum's, even), I'm using acs objects and a flat (non branched) representation of the "object order" via a sort_order column (in some packages referred to as "sort_key") ...

What about the value that goes into the sort_order column, should it be generated from a sequence or a "max(sort_order) + 1" hack?

I thought I'd toss this question on the forum so if folks had a preference for one or another solution (using objects), I could at least narrow down on the amount of code I'd have to study. 😉

Thanks Dave!

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.

To use cr_child_rels to order a specific content_type it would probably be useful to create a view that would return the items in the proper order based on the parent.

This would be very useful if, for example you have an article with 3 related images. The editor could change the order the images appear with that article.

Then some or all of those images could also be related to another article with a different order.

The cr_child_rels method might be more complex, but offers the best flexibility. I agree that it is a good idea to improve the API to manage this and the rest of the features of the content repository.

Ola,

If you aren't using the CR you have two options, i think. Put an explicit sort_order in your items table if the items will only be ordered one way. Otherwise you need a mapping table that contains a parent_id and sort_order. You could use acs_rels or a custom mapping table.

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.

Duh! I should have said "where curriculum_id = :curriculum_id" instead so that it would update more than just one single row ...

Also, "case" works well on Oracle 8.1.7 so that means only one common .xql file, woohoo!

One should probably select the closest higher or lower sort_key into "next_sort_order" as opposed to counting on it being +/- 1 the given sort_order in case someone deletes elements in between, oh well.