How to keep column 'order' in sequence even after editing the order of records?

Question

I have a table with n fields, one of which is the 'order' (obviously it is sequential: 1, 2, 3, n...):

id  |  ordem  | nome
------------------------
1   |  1      | Fabio
3   |  2      | Gilmar
2   |  3      | Rodrigo
9   |  4      | Marcos  

I am developing a drag and drop table to change this order, so how do I always keep this sequence without skipping any number and nor repeat them?

To make it difficult, there will also be manual order, that is, the user accesses the record and selects the desired position. Therefore, all the records below this should be changed.

Note: My system provides 4 parameters after dragging: id, Direction (forward/back), fromPosition and toPosition that could be used in the query.

Answer 1

My suggestion would be the column ordem not be sequential, but rather be a field that normally increases by 10.

For example:

id  |  ordem  | nome
------------------------
1   |  10     | Fabio
3   |  20     | Gilmar
2   |  30     | Rodrigo
9   |  40     | Marcos 

To change position, increase target position by 5:

id  |  ordem  | nome
------------------------
1   |  10     | Fabio
3   |  25     | Gilmar
2   |  30     | Rodrigo
9   |  40     | Marcos 

Then move the source position to the position that has been changed:

id  |  ordem  | nome
------------------------
1   |  10     | Fabio
9   |  20     | Marcos <-- update de ordem para 20
3   |  25     | Gilmar
2   |  30     | Rodrigo

Then just rebalance the other lines:

id  |  ordem  | nome
------------------------
1   |  10     | Fabio
9   |  20     | Marcos
3   |  30     | Gilmar
2   |  40     | Rodrigo

Answer 2

Ideally you would somehow know the new order of the item in question, and do 2 updates:

update TABELA set ordem = ordem + 1 where ordem > 7 (para acrescentar 1 para os itens acima da nova ordem)

update TABELA set ordem = ordem - 1 where ordem < 7 and ordem <> 0 (para decrescer 1 para os itens abaixo da nova ordem)

With that order 6 would be free ... something like that. You have to go more or less this way.

Answer 3

I understand you’re dragging names from one position to another. In this case you would only need a pair (Id, Order)Origin and (Id, Order)Destination.

Following his example:

id	order	name
1	1	Fabio
3	2	Gilmar
2	3	Rodrigo
9	4	Marcos

By dragging Marcos to the Fabio position you would make a couple of updates by switching positions

update Tabela set Ordem = 4 where Id = 1
update Tabela set Ordem = 1 where Id = 9

Resulting in:

id	order	name
1	4	Fabio
3	2	Gilmar
2	3	Rodrigo
9	1	Marcos

As BD tables have no order intrinsic you (should always) declare the ordination.

Select Id, Ordem, Nome from Tabela order by Ordem

Hence your result will always be returned correctly.

EDIT

In case you want to drag a name to a position and keep others in their relative positions is a little more complicated.

Following the example of dragging Marcos to Fabio’s position. You would have to set Mark to order 1 and to everyone between 1 and Mark’s old position increment from 1 (because you dragged from the bottom up). If similar way drag Fabio to the position of Marcos you would have to set Fabio to 4 and decrease the position of everyone below Fabio to Marcos

Answer 4

You can swap... take the value fromPosition, use this value to change the value of the order of the record that is in toPosition, then change the record that was actually changed... that way, you have the records simply "exchanged" for each other, without losing their sequence.