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.

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

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?

tgh003@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)

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

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)

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

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)

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

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/