Re: reordering lists

I am adding a general comment on the 3 replies thus far. It seems that splice/slice might be a solution, but I have to figure out how to implement that in a database.

Adding another column to the table to store the sort order may be an idea, but changing the sort order of any one element will entail recalculating the sort order of the all subsequent elements in the table. This is the original "thorny" problem that I am trying to circumvent.

One way I thought up would be to create a sort order that is not sequential but jumps by an increment of, say, 100 or 1000... the way we used to number lines in BASIC... to avoid renumbering lines in case a line has to be moved. So, I would create a column for storing the sort order, but instead of storing 1, 2, 3... I would store 1000, 2000, 3000... Then, if the element 45 with the sort order value 45000 had to be repositioned at slot 3, I would just renumber its sort order value to 2500 (leaving space on both sides, before/downto 2000, and after/upto 3000 without having to recalculate any other element.

Of course, this is inelegant as heck, and is not scalable. But might work.

Any thoughts?

Btw, I am also reading up on linked lists in the hope they may offer some salvation.

Comment on Re: reordering lists

Replies are listed 'Best First'.
Re: Re: reordering lists by revdiablo (Prior) on Jan 20, 2004 at 21:25 UTC
I am also reading up on linked lists This seems like it might be the way to go. A linked list, based on my understanding, is just a bunch of items that have pointers, aka links, to their neighbors. Perhaps one could use a table with columns `THIS_ID, PREV_ID, NEXT_ID`. Then only a few columns need to be changed in order to remove an item, add a new item, or move an item to a different location. The problem with this approach, as I see it, is you then have to step through the whole thing sequentially in order to build your final ordered list. Perhaps there are some techniques to avoid this that I'm unaware of (I do not have a computer science background; I'm just a long-time amateur programmer). If there are any other ideas, I'd love to hear them. It's always an education. 8^) Update: to clarify a bit, I should have mentioned that I agree with some of the monks who say the order should be separate from the definition of the images. In other words, you might have one table called `IMAGES` with columns `IMAGE_ID, IMAGE_NAME, IMAGE_SIZE, IMAGE_COMMENTS` then a separate table named `IMAGE_ORDER` with the `THIS_ID, PREV_ID, NEXT_ID` columns.	[reply] [d/l] [select]
Re: Re: reordering lists by paulbort (Hermit) on Jan 21, 2004 at 23:00 UTC
Only half-serious: You can solve the problem of needing to put picture 5 between pictures 2 and 3 by assigning it sequence 2.5. Unlike the BASIC numbering method, scalability is only limited by the accuracy of your floating-point package. :-) Now for a real-world answer: If you don't care about normalization, use separate tables for house and pictures, and put a big nasty text column on the house table. Put the comma-separated list of pictures for that house in that column, in the order they should be displayed! This will work until there are more pictures for a house than can fit in that column. Base64 numbering would help with this, reducing the column size to two chars per picture for the first 63 pictures. (one data, one separator, assume no use of 0). If you can guarantee an upper limit on the number of pictures (like 64^2-1), you can skip the separator and use unpack(). Re:Linked Lists Linked lists are a classic solution to this problem in memory, but don't solve the problem of one update for each picture when you want to save the order to the database, unless you use Storable to just dump the whole list into a column, which is not much different from my suggestion above. -- Spring: Forces, Coiled Again!	[reply]