Обсуждение: most efficient way to manage ordering

Sorry if this is confusing, it is somewhat difficult to explain.

I find myself frequently creating solutions to the same problem.  I'm not
yet happy with the way I've done any of them and I'd like to find a purely
SQL way of doing this if possible.

Here's what I have.  For a contrived illustration, let's say we have a
database of photo galleries, each having some number of images.  Our tables
would look like this:

galleries
-------------------------
galleryid    | int4 (pkey)
name        | text


images
-------------------------
imageid    | int4 (pkey)
galleryid    | int4 (fkey)
image        | text
dsply_order    | int4 (index)


Now, the same database holds many different galleries.  Each gallery has
some number of images and the users want the images to show in a certain
order. This is done by inserting the images in the order you want them to
appear.  However, it may be necessary for the user to re-order them.  I
provide a MOVE UP, MOVE DOWN, MOVE TO TOP and MOVE TO BOTTOM option so that
they can change the order.  Also, people occasionally delete images.

If a person maintaining galleryid 1 which has 6 images, a "SELECT * FROM
images WHERE galleryid = 1 ORDER BY dsply_order" might show this:
imageid | galleryid | image      | dsply_order
--------+-----------+----------+------------
4      | 1          | 1/me.gif | 1
7      | 1          | 1/aa.gif | 2
12      | 1          | 1/bb.gif | 3
11      | 1          | 1/cc.gif | 4
15      | 1          | 1/dd.gif | 5
18      | 1          | 1/ee.gif | 6

Now, when a person decide to re-order, it's no problem to do this:
To move imageid 12 to the top:
UPDATE images SET dsply_order = CASE WHEN imageid = 12 THEN 1 ELSE
dsply_order + 1 END WHERE galleryid = 1;

That however leaves a gap at dsply_order 4: 
imageid | galleryid | image      | dsply_order
--------+-----------+----------+------------
12      | 1          | 1/bb.gif | 1
4      | 1          | 1/me.gif | 2
7      | 1          | 1/aa.gif | 3
11      | 1          | 1/cc.gif | 5
15      | 1          | 1/dd.gif | 6
18      | 1          | 1/ee.gif | 7

Similar problem occurs when someone deletes an item.

Ideally, I'd like to figure out a single SQL query that can be run
afterwards to clean up the dsply_order to make sure that each number occurs
only one time and that there are no gaps.

I know I can write a sp for this, but the problem is, I do this very task on
lots of different tables that all have different formats and different types
of data.  I'd rather not have to maintain many different procedures if I can
find an alternate.

Right now, I get the job done in code, but it feels inefficient.

Matthew Nuzum        | ISPs: Make $200 - $5,000 per referral by
www.followers.net        | recomending Elite CMS to your customers!
matt@followers.net    | http://www.followers.net/isp

> Ideally, I'd like to figure out a single SQL query that can be run
> afterwards to clean up the dsply_order to make sure that each number occurs
> only one time and that there are no gaps.

Well... by far the easiest way to approach this is not to clean up the
gaps. Removing gaps will only make things pretty, not easier or faster.

This is one of the many times it is best to differentiate between what
is displayed and what is used for functional purposes.
       CREATE TEMPORARY SEQUENCE images_display_count;         SELECT nextval('images_display_count') AS display_order
           , *           FROM images          WHERE galleryid = 1       ORDER BY real_order;       DROP SEQUENCE
images_display_count;      
 
There are ways of replacing the sequence that may be faster, but this
will address your concern. Do your updates, etc. via real_order and show
the user display_order.