473,320 Members | 2,092 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

reordering list help or algo needed

So lets assume I have a list of tasks in db table (table looks like:
ID, task, sort)

And I want to reorder the task list without updating all the records in
the table. I dont want to run a sql update stmt against every record to
update the "sort" field.

Any other easy way to do this? there has got to be a simple algo out
there.

any help is greatly appreciated.

Jul 17 '05 #1
10 4244
Return 'task' in accending order of 'sort' field
SELECT task FROM db.table ORDER BY sort

Retun 'task' in decending order of 'sort' field
SELECT task FROM db.table ORDER BY sort DESC

Jul 17 '05 #2
Yes i know about ORDER BY. That is not the issue.

When displayed using a UI, i want the user to be able to reorder the
list by moving a task up or down based on priority.

I then need to save those changes to the database, and in order to do
this i would need to reupdate all the records. This is obviously not
very scalable/efficient to do this everytime there is a change assuming
the task list is long.

any other ideas?

Jul 17 '05 #3
tg****@gmail.com wrote:
Yes i know about ORDER BY. That is not the issue.

When displayed using a UI, i want the user to be able to reorder the
list by moving a task up or down based on priority.

I then need to save those changes to the database, and in order to do
this i would need to reupdate all the records. This is obviously not
very scalable/efficient to do this everytime there is a change assuming
the task list is long.

any other ideas?


How many items? If a dozen, even a hundred, an update to the entire table
is sub-second. Of course I will deny ever having made the suggestion if
you run into any trouble.

How many columns in the table? Another strategy is to directly swap the
values of all columns except "sort" in a single transaction:

begin transaction
update priorities set id=ValFromRow15,task=ValfromRow15 where sort=3
update priorities set id=ValFromRow3,task=ValfromRow3 where sort=15
commit

You may have trouble with ID, hmmm. What is that column doing anyway?

It also seems you may want the client to force only certain types of moves
so you don't have a big jumble to sort out when you try to commit. Perhaps
allow the user to adjust the priority of only one row at a time before a
trip to the server, so at most you are only ever swapping two rows.

--
Kenneth Downs
Secure Data Software, Inc.
(Ken)nneth@(Sec)ure(Dat)a(.com)
Jul 17 '05 #4
One quick glance of an experienced eye allowed to understand the blurred
and almost unreadable Kenneth Downs's handwriting:
It also seems you may want the client to force only certain types of
moves so you don't have a big jumble to sort out when you try to
commit.**Perhaps allow the user to adjust the priority of only one row
at a time before a trip to the server, so at most you are only ever
swapping two rows.


Hmmm... And what will happen, when a user needs to update, say, 20 rows?
Clicking, query, clicking, query, clicking, query again and again - this
is both not ergonomic and very slow - just think how many times slower
than letting the user update all the rows in a single query.

Just my three pence.

Cheers
Mike
Jul 17 '05 #5
Micha? Wo?niak wrote:
One quick glance of an experienced eye allowed to understand the blurred
and almost unreadable Kenneth Downs's handwriting:
It also seems you may want the client to force only certain types of
moves so you don't have a big jumble to sort out when you try to
commit.**Perhaps allow the user to adjust the priority of only one row
at a time before a trip to the server, so at most you are only ever
swapping two rows.


Hmmm... And what will happen, when a user needs to update, say, 20 rows?
Clicking, query, clicking, query, clicking, query again and again - this
is both not ergonomic and very slow - just think how many times slower
than letting the user update all the rows in a single query.


It's all in the UI design.

Idea one. Non-optimum but easy to code. User clicks on a row in a list of
tasks. User clicks "move up". Trip to server, list is refreshed, row is
in new spot. No click/query/click/query, just click/click/click. Tedious,
but as I said, probably easiest to code.

Idea two. User sees list of rows. User clicks checkbox for row to move.
User clicks row to move it ahead of. Trip to server, list is refreshed,
row is in new spot.

The bottom line is that any UI design will have the same basic operations,
the only real question is when a trip to the server happens. A good
programmer will make it happen without losing context for the user.

--
Kenneth Downs
Secure Data Software, Inc.
(Ken)nneth@(Sec)ure(Dat)a(.com)
Jul 17 '05 #6
One quick glance of an experienced eye allowed to understand the blurred
and almost unreadable Kenneth Downs's handwriting:
It's all in the UI design.

Idea one. Non-optimum but easy to code. User clicks on a row in a
list of
tasks. User clicks "move up". Trip to server, list is refreshed, row
is
in new spot. No click/query/click/query, just click/click/click.
Tedious, but as I said, probably easiest to code.

Idea two. User sees list of rows. User clicks checkbox for row to
move.
User clicks row to move it ahead of. Trip to server, list is
refreshed, row is in new spot.


When I was saying about click/query/click/query I didn't mean "User
clicks, user says to query, user clicks, users says to query" - I was
talking about "User clicks, script makes the trip to server, user
clicks, script makes the trip to serwer". What about if there is n users
and they all want to update m rows each? We'll get n*m trips to serwer
(even a little more). If the user could say "Update rows this, that,
those and the other one" we'd ged only about n trips to serwer.

Cheers
Mike
Jul 17 '05 #7
Micha? Wo?niak wrote:
One quick glance of an experienced eye allowed to understand the blurred
and almost unreadable Kenneth Downs's handwriting:
It's all in the UI design.

Idea one. Non-optimum but easy to code. User clicks on a row in a
list of
tasks. User clicks "move up". Trip to server, list is refreshed, row
is
in new spot. No click/query/click/query, just click/click/click.
Tedious, but as I said, probably easiest to code.

Idea two. User sees list of rows. User clicks checkbox for row to
move.
User clicks row to move it ahead of. Trip to server, list is
refreshed, row is in new spot.


When I was saying about click/query/click/query I didn't mean "User
clicks, user says to query, user clicks, users says to query" - I was
talking about "User clicks, script makes the trip to server, user
clicks, script makes the trip to serwer". What about if there is n users
and they all want to update m rows each? We'll get n*m trips to serwer
(even a little more). If the user could say "Update rows this, that,
those and the other one" we'd ged only about n trips to serwer.

Cheers
Mike


Its a c/s app, you always balance need to go to server vs. desire to save a
trip. I gave some possibilities for thought, the rest is up to tgh003.

--
Kenneth Downs
Secure Data Software, Inc.
(Ken)nneth@(Sec)ure(Dat)a(.com)
Jul 17 '05 #8
ya i am looking for some more efficient ideas - i think i got one but
need to flush it out to make sure itll really work, ill share what i
came up with when i got it.

thx

Jul 17 '05 #9
tgh...@gmail.com wrote:
ya i am looking for some more efficient ideas - i think i got one but
need to flush it out to make sure itll really work, ill share what i
came up with when i got it.


FWIW, <http://www.google.com/search?q=javascript+table+sort>

--
<?php echo 'Just another PHP saint'; ?>
Email: rrjanbiah-at-Y!com Blog: http://rajeshanbiah.blogspot.com/

Jul 17 '05 #10
NC
tg****@gmail.com wrote:

So lets assume I have a list of tasks in db table (table looks like:
ID, task, sort)

And I want to reorder the task list without updating all the records
in the table. I dont want to run a sql update stmt against every
record to update the "sort" field.

Any other easy way to do this?
Nope; UPDATE is simple enough. You may want to keep your task list
in an InnoBD table, so that reordering could be done as a transaction.
Say, you want to move the tenth task up to the second spot:

BEGIN;
UPDATE the_table SET sort=0 WHERE ID=XXX AND sort=10;
UPDATE the_table SET sort=sort+1 WHERE ID=XXX AND task>=2 AND task<10;
UPDATE the_table SET sort=2 WHERE ID=XXX AND sort=0;
COMMIT;

(I am assuming all records in the same task list have the same ID.)
there has got to be a simple algo out there.


What's wrong with UPDATE queries? You only need three of them
regardless of the number of items in the list...

Cheers,
NC

Jul 17 '05 #11

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Daniele Varrazzo | last post by:
If you need a container to look into, there is the sets module that provides a couple of them. If you need a sorted list, there is the bisect module. But i don't think it fits your need for a...
0
by: Mel Draper | last post by:
Hello all, I have tried searching for this. I need a listbox populated by my database table where users can re-order the list of items and submit the changes. I can do the listbox and populate...
11
by: Laszlo Zsolt Nagy | last post by:
Hello, Do you know how to implement a really efficient self reordering list in Python? (List with a maximum length. When an item is processed, it becomes the first element in the list.) I would...
4
by: Nathaniel Price | last post by:
I'm new to this list, so I'm not sure if this is the right place to post this. If not, please direct me to where it would be better to post it. Anyway, I'm creating a report generation tool of...
2
by: greenflame | last post by:
I am trying to reorder elements of a list and I am stuck as to what might be the best way to approach this. I have a (main) list of elements and another (ordering) list (which is may shorter, but...
67
by: Philippe Martin | last post by:
Hi, I'm looking for an algo that would convert a list such as: I'm using python to prototype the algo: this will move to C in an embedded system where an int has 16 bits - I do not wish to use...
5
by: Steven T. Hatton | last post by:
If find the following excerpt from the Standard a bit confusing: <quote> 3.3.6 - Class scope -1- The following rules describe the scope of names declared in classes. 1) The potential scope...
3
by: ravi | last post by:
Can any body tell me an effective algo to sort a doubly link list algo must be time as well as memory effective
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.