1

I have a table containing articles. By default, the articles are sorted based on their date added (desc.) so newest articles appear first.

However, I would like to give the editor the ability to change the order of the articles so they can be displayed in the order he likes. So I am thinking of adding an integer "order" column.

I am in a dilemma of how to handle this as when an article's order is edited, I don't want to have to change al the others.

What is the best practice for this problem? and how other CMS like Wordpress handle this?

Machavity
  • 30,841
  • 27
  • 92
  • 100
TestCandidate
  • 166
  • 7
  • All the values will need to be changed if the user changes the order for all the articles. Not an expert. There may be better ways of doing it. – clinomaniac Nov 27 '17 at 22:00
  • I think I covered something like this in SQL Server a long time ago... https://stackoverflow.com/questions/8607998/using-a-sort-order-column-in-a-database-table/8608085#8608085 I would think a similar approach would work in mySQL. It's actually not bad to update all records as it's done in a set based processes. Now if you're talking millions of records I could see why this would start to get slow. Alternatively you could say add days to the date and if negative you subtract that may to put it in the desired order or + that may to move it into future... – xQbert Nov 27 '17 at 22:03
  • There must be a better practice. This would be horrible when having 1000+ article. I saw an algorithm where i can skip 1000 order value between each entry. ie: article 1-> oder 1000. article 2 -> order 2000. Etc. But I wanted advises on what is a good, scalable practice. – TestCandidate Nov 27 '17 at 22:03
  • Furthermore, you could have the UI allow the user to drag/drop the order and the system calculates the date difference between the two and subtracts the needed milisecond for the sort; then you `coalesce(sortDate,ArticleDate) when sorting` Note this means a new date field on table for order. – xQbert Nov 27 '17 at 22:08
  • @xQbert Thanks for the advice. I'm checking your aproach linked above. Drag and drop is not an option because there will be a big num of articles and they'll be displayed in a 'paging' manner. So it'll all just be painful. I thought this was an easily solved problem as this is a common feature. Surprisingly it is still a good challenge – TestCandidate Nov 27 '17 at 22:13
  • I've seen some cases where the order column used a FLOAT instead of an INT. That way you can nearly always set a floating-point value that is between two other values, even if you have to go down to values with large negative exponents. – Bill Karwin Nov 27 '17 at 22:52
  • How would the initial order value for a row be determined? – Uueerdo Nov 27 '17 at 23:03

2 Answers2

0

Updating the records between the moved record's original position and it's new position might be simplest and most reliable solution, and can be accomplished in two queries assuming you don't have a unique key on the ordering column.

The idea suggested by Bill's comment sounds like a good alternative, but with enough moves in the same region (about 32 for float, and 64 for double) you could still end up running into precision issues that will need checked for and handled.

Edit: Ok, I was curious and ran a test; it looks like you can half a float column 149 times between 0 and 1 (only taking 0.5, .25, .125, etc... not counting .75 and the like); so it may not be a huge worry.

Edit2: Of course, all that means is that a malicious user can cause a problem by simply moving the third item between the first and second items 150 times (i.e "swapping" the 2rd and 3rd by moving the new third.

Uueerdo
  • 15,723
  • 1
  • 16
  • 21
0

More challenging is the UI to facilitate the migration of items.

First, determine what the main goal(s) are. Interview the Editors, then "read between the lines" -- they won't really tell you what they want.

  • If the only goal is to once move an item to the top of the list, then you could simply have a flag saying which item needs to come first. (Beware: Once the Editors have this feature, they will ask for more!)
  • Move an item to the 'top' of the list, but newer items will be inserted above it.
  • Move an item to the 'top' of the list, but newer items will be inserted below it
  • Swap pairs of adjecent items. (This is often seen in UIs with only a small number of items; not viable for thousands -- unless the rearrangement is just localized.
  • Major scrambling.

Meanwhile, the UI needs to show enough info to be clear what the items are, yet compact enough to fit on a single screen. (This may be an unsolvable problem.)

Once you have decided on a UI, the internals in the database are not a big deal. INT vs FLOAT -- either would work.

INT -- easy for swapping adjacent pairs; messier for moving a item to the top of the list. FLOAT -- runs out of steam after about 20 rearrangements (in the worst case). DOUBLE would last longer; BIGINT could simulate such -- by starting with large gaps between items' numbers.

Back to your question -- I doubt if there is a "standard" way to solve the problem. Think of it as a "simple" problem that can be dealt with.

Rick James
  • 135,179
  • 13
  • 127
  • 222