Jason,
Your main concern is the overhead involved in updating the item order, so you
definitely want to avoid doing updates inside a cfloop or mass deletes/inserts.
Moving one item up or down is no problem, because all you need to do is to swap
the sort order index and only touch 2 records.
The challenge is when you move an item to top or bottom where in the worst case
you have to update every record in order to shift their order up or down by
one. Fortunately you can do this in a single DB update. Modern databases are
very efficient in doing this and the overhead will be very small, as long as
you do everything within a single query. Something like this to move the
selected item to the top:
UPDATE ITEMS
SET OrderIndex = OrderIndex + 1
WHERE ItemID < #SelectedItemID#
Just reverse the logic to move the selected item to the bottom.
Remember, CFLOOPs ist verboten!
Regards: Ayudh
+----------------------------------------------------------------+
| SOAP is the glue! Hook up your server directly to your bank. |
| Connect to VeriPay xServ, the Australian Payments Web Service. |
| Reliable, Secure, FAST: http://www.xilo.com/xserv |
+----------------------------------------------------------------+
Jason Sheedy wrote:
> I'm creating a schedule application for our intranet and am looking for
> the most efficient way to list items by priority. The basic idea is that
> you can move items up,down, to top and to bottom in the priority list.
>
> Currently I store the priority as an integer and order by the priority
> field when displaying the list. However, there's quite significant
> overhead in updating each record when changing the priority. i.e. if I
> want to move the bottom item to the top I need to update every record with
> a new priority value.
>
> The alternative way I was thinking of doing it was by simply storing the
> parent id of each item in a linked list. However, the overhead in
> maintaining this is also quite significant.
>
> Anyone got any better ideas on how to do this?
>
> cheers,
>
> Jason Sheedy
> www.jmpj.net
---
You are currently subscribed to cfaussie as: [email protected]
To unsubscribe send a blank email to [EMAIL PROTECTED]
Aussie Macromedia Developers: http://lists.daemon.com.au/
