Hi Jason. I'm in the same boat as you, except I use an ordering system where I move orders up or down, or allow the operator to specify a new order.
Problem is that all records are related to each other in some sort of relationship. It's not that you are saying this value in this record equals something (such as a date or dollar value), and therefore its order changes in relation to all other values in the same field for other records (such as ordering by date/time or total dollar value). It's that the order or priority value itself is related to all other records. Record 1500 is intrinsically related to record 3 in that it is 1497 records away. I move record 3 in any way, I need to find some way to tell all the records between 3 and 1500 that the relationship has changed. I don't know of any way to do this other than store this relationship in each record as a number to signify the nature of that relationship. I guess a problem could arise if people are constantly changing the orders, or priorities. But my guess would be that the use case of "people changing priorities" does not happen in such frequency to warrant the 2.37 man hours we have all spent reading and writing about this topic. :P Chad who is learning UML and wanted an excuse to show off by using "use case" in a sentence On 4/14/05, Ayudh Nagara <[EMAIL PROTECTED]> wrote: > > > 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/ > --- 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/
