CODEWITHSUNDEEP
sqlsqlite
Jenya Kyrmyza
Jenya Kyrmyza

Reputation: 327

order in database table

I have sqlite table called notes. It has id, title, text.

In my application user can place notes in the order he wants. He can drag and move notes changing their positions.

First, i thought to create "position" field. But it's bad idea. If one note has changed it's position, the other notes also will change their positions. So e.g. if it's gonna be one thousand records, then it's not good idea at all changing all the records because of one action.

How can i solve this thing, so user will change the note position and will save this state to the database?

Upvotes: 1

Views: 426

Answers (3)

Harman
Harman

Reputation: 356

The database will need another column that contains the user's preferred order. Adding another column is not a bad idea: add a new column, say ListOrder, that holds an integer datatype.

Display notes in order of increasing ListOrder values.

     Select * from Table Order By ListOrder

If the user drags a note up by one unit in the UI, then increment the integer in ListOrder for that row by 1. 

     Update Table Set ListOrder=ListOrder+1 Where ListOrder=(Select ListOrder-1 From Table where Column_x = 'blob')

Note that column_x is another aspect of the row containing some identifying datatype, such as a username.

Upvotes: 1

Ramiro Alarcón
Ramiro Alarcón

Reputation: 29

I think the best way to do that is saving the final positions, at end, when the user will no make more changes. A full scan or something.

Upvotes: 0

Bitte Wenden
Bitte Wenden

Reputation: 100

Just store the previous and next node. Then you can always look up the order. Or you just always only save the node.

Upvotes: 1

Related Questions