By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,115 Members | 898 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,115 IT Pros & Developers. It's quick & easy.

reordering list help or algo needed

P: n/a
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
Share this Question
Share on Google+
10 Replies


P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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 discussion thread is closed

Replies have been disabled for this discussion.