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
Message-ID: <200406020235.i522Zrnb015405@ms-smtp-02.tampabay.rr.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: cobalt@bearfruit.org
Subject: Re: most efficient way to manage ordering
In-Reply-To: <200406020235.i522Zrnb015405@ms-smtp-02.tampabay.rr.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox