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.

re: reordering list help or algo needed

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

re: reordering list help or algo needed

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?

re: reordering list help or algo needed

tgh003@gmail.com wrote:
[color=blue]
> 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?[/color]

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)

re: reordering list help or algo needed

One quick glance of an experienced eye allowed to understand the blurred
and almost unreadable Kenneth Downs's handwriting:
[color=blue]
> 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.[/color]

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

re: reordering list help or algo needed

Micha? Wo?niak wrote:
[color=blue]
> One quick glance of an experienced eye allowed to understand the blurred
> and almost unreadable Kenneth Downs's handwriting:
>[color=green]
>> 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.[/color]
>
> 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.
>[/color]

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)

re: reordering list help or algo needed

One quick glance of an experienced eye allowed to understand the blurred
and almost unreadable Kenneth Downs's handwriting:
[color=blue]
> 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.[/color]

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

re: reordering list help or algo needed

Micha? Wo?niak wrote:
[color=blue]
> One quick glance of an experienced eye allowed to understand the blurred
> and almost unreadable Kenneth Downs's handwriting:
>[color=green]
>> 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.[/color]
>
> 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[/color]

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)

re: reordering list help or algo needed

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

re: reordering list help or algo needed

tgh...@gmail.com wrote:[color=blue]
> 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.[/color]

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/

re: reordering list help or algo needed

tgh003@gmail.com wrote:[color=blue]
>
> 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?[/color]

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.)
[color=blue]
> there has got to be a simple algo out there.[/color]

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

Cheers,
NC