Message-Id: <200406020235.i522Zrnb015405@ms-smtp-02.tampabay.rr.com>
From: "Matthew Nuzum" <cobalt@bearfruit.org>
To: <pgsql-sql@postgresql.org>
Subject: most efficient way to manage ordering
Date: Tue, 1 Jun 2004 22:35:52 -0400
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
Thread-Index: AcRISlKiXb73cCFFRzqZDohkAAzi0A==

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