CODEWITHSUNDEEP
phpmysqlperformance
Gowri
Gowri

Reputation: 16845

Best way to list the items by order

I want to list items by defined order.so my table has order column it'll keep the order number. so, i can list items using that order number.If the number of items are less means no problem,But while it is getting more. i'm getting troubles.

Item table:-

id   item varorder
1     A1    1
2     A2    2
3     A3    5
4     A4    3
5     A5    4
......
1000  A1k   1000

Using varorder, I can list items in defined order like bellow

SELECT item FROM tbl_item ORDER BY varorder ASC

The query results me correct order of items like A1,A2,A4,A5,A2,...,A1K

My Problems

Problem occurring while i try to make A1K varorder as 1.

enter image description here

So suggest me. Is there another good idea to keep order in defined order ?

Thanks

Upvotes: 2

Views: 265

Answers (4)

Maxim Krizhanovsky
Maxim Krizhanovsky

Reputation: 26699

The updating of rows will be problem is they are millions, not 1000. In that case, you can have varorder and orderupdated, and order by varoder ASC, orderupdated DESC. In this way, when 2 records are with the same order (1), the one that was set to 1 later will be first, and the other will be second.

Upvotes: 0

aroth
aroth

Reputation: 54816

You might consider a sparse ordering, like:

id   item varorder
1     A1    10000
2     A2    20000
3     A3    50000
4     A4    30000
5     A5    40000

Then you can move an element in front of or in between any other element in the list without having to update every row in the table. I'd suggest an algorithm that appends to the end of the list using a varorder that is greater than the previous varorder by at least 10,000, and that handles an insertion between two existing elements by assigning the inserted element a varorder that is halfway between the varorder's of the two existing elements.

So with the above example, moving A3 to the second position in the list would yield:

id   item varorder
1     A1    10000
2     A2    20000
3     A3    15000
4     A4    30000
5     A5    40000

With a very large number of relocations this approach will eventually run out of space in the sparse index, so the question you have to ask yourself is whether or not you think there will ever be that many reorder operations in practice.

Upvotes: 0

TheHorse
TheHorse

Reputation: 2797

About UI: drag and drop = best way.

About updates: You can use varcodes like: 256 512 768 1024... So when you want to move some element you update his number to (lefter + righter) / 2. At cases like lefter = 1; righter = 2; (lefter + righter) / 2 = 1.5; you have to run some procedure which will update all element to (256 512 768 1024...) according to current sorting.

Upvotes: 0

Scott C Wilson
Scott C Wilson

Reputation: 20026

Add a column "sortdatechanged" and make it now() on insert. Then update it when you hit the up arrow. When you hit the down arrow, update varorder to max(varorder)+1, and set sortdatechanged to min(sortdatechanged). Then select by sortdatechanged desc, varorder.

Upvotes: 1

Related Questions