I encounter this issue when I create REST API.

I cannot tell the exact scenario, but let's say it is for a website that has category and product resources.

Each category has many products and both category and product are ordered.
I originally add an order incremental integer attribute to the resources so both collections can be displayed in correct order.

The problem with my original approach is when user drags and drops a product, a lot of other product's order attributes need to be changed. (When you move the 10th product into 1st place 10 order attributes need to be updated) This is awful for REST API and the database itself.

Is there any way to create an attribute to order product (or model the collection with other approach) so that drag and drop ordering can be done with a single (or few in scalable way) resource items changed?

The answer to the question above without any more condition is good but it would be great if it also works when user drag product from one category and drop it in other category.

  • 1
    Why does the user's ability to drag-and-drop items in your UI have any bearing on your database schema? – Ixrec Nov 21 '15 at 16:39
  • @Ixrec I don't really understand your question but I would say because the change of the state will certainly effect the database. I actually don't cares about drag and drop thing it is just a metaphor to makes the situation clearer. – Curious Sam Nov 21 '15 at 16:49
  • 1
    Another try then: Why does the user rearranging items in the UI have to be saved to the database? Why can't it be purely client-side state that disappears when the application is closed? Or if the user really is in charge of deciding the sort order for all items in the database, why are they in charge of that, and why is this "awful" for your API/database? We need to understand what you're trying to do before we can suggest any solutions to your problem; based solely on what you've already written my only answer would be "you shouldn't save transient UI state to the database". – Ixrec Nov 21 '15 at 16:59
  • @Ixrec It need to be persist because the one who set the value is the one who in charge of arranging them. It awful for API because the API is RESTful so clients need to call the API many times per single ordering update and I cannot guarantee data correctness that way. I cannot put unique constrain on compounded order attribute and the operation is not atomic. – Curious Sam Nov 21 '15 at 17:17
  • It is awful for database even if I can find a way to to expose the operation I described into single call to the API and still call it "RESTful API" (which I don't know a way to do that yet) I can use transaction to make it atomic but multiple rows in the database need to be updated anyway. – Curious Sam Nov 21 '15 at 17:17

To avoid updates to multiple rows, don't store the exact index in the database. Instead, store a value that is used for sorting only. When rearranging the order, give the inserted item a value halfway between the preceding and subsequent items. Using a floating point value will give you plenty of "empty slots" between items.

For example, given three items (A, B, C) valued 1.0, 2.0, and 3.0. The user changes the third item (C) to be second. Update the sorting value of C to be between A and B, resulting in A, C, B valued 1.0, 1.5, 2.0, respectively.

The trade-off here is that the index must be calculated from the whole list. Looking at just a single item, the value tells you nothing about the location of that item in the list.

  • This approach works it is actually what I think after failure of integer order. The idea is to use floating number as ordering sequence. I went further by porting the idea to integer attribute by bit shifting new item added to make room for rearrangement, but something is still feel not right to me. I just curious is there any better way. I will upvote this answer anyway if I have enough reputation to do so but this is not what I expected yet. – Curious Sam Nov 21 '15 at 17:55
  • 1
    Interesting...so if a product has been dragged 12 times, its sort value could be 2.555555555555? – John Doe Nov 21 '15 at 17:56
  • I am concerned that if you knew this was in use, you could keep reordering specific items until you got to the precision limit of the data type. Putting a new item at the top (its a bookmark for me, its what I'm working on now) with the initial top item being 1.0 would get it converging to 0 (and then break). – user40980 Nov 24 '15 at 15:11
  • @MichaelT true, you'd need to verify there was an available "slot" before – Nathan Gerhart Nov 26 '15 at 3:22
  • @bigdogwillfeed and if there's not, theres a big chunk of math you'll need to do. For that matter, verifying if there's a slot is a big chunk of math. Though generally, having used preorder trees before, I haven't found the update order = order + 1 where order > 5 to be that taxing on a database with even a few dozen entries - the human gets lost before the database does. If you're sorting a few hundred, then I really need to question the order in the first place. – user40980 Nov 26 '15 at 3:24

First of all, the product and category resources that can be accessed/viewed through your REST API do not have to mirror your database tables. In particular, the order of the products is not a property of a single product resource, but on the REST API it should be a property of the collection of products. This means that it should take only a single API call to change the order of the products.

In your database, you can then either use an array index column or a column that contains a link to the next product. As this implementation detail isn't exposed on the API, you can even change your mind later on and change how the ordering is stored in the database without affecting your clients.

  • The API part is very convincing but I don't see how API request will looks like in practice. The database part did not have much detail but made me realize something in John Doe answer. I will address the problem of linked list there. – Curious Sam Nov 21 '15 at 18:21
  • 1
    @CuriousSam, there are multiple ways to implement this. For example, you could do a PATCH of the collection resource, which contains the new sort index for each element. For example, PATCH /categories: [ { "id" : "categ1", "sortIndex" : "2" }, { "id" : "categ2", "sortIndex" : "1" }]. – www.admiraalit.nl Nov 21 '15 at 18:40
  • Bart may be onto something. If you have a "ProductCategory" table in your database with a column named "ProductsOrder" that is a varchar...it could contain a value like "221,387,45,130,757,192,655,408". And you would just have to make 1 SQL UPDATE statement to move the last to first...setting the new value to "408,387,45,130,757,192,655". You can do all your sorting/calculating in memory with an array before you update the database. The processing time in memory for the sorting will be trivial and it will be just 1 database call. – John Doe Nov 21 '15 at 19:10
  • @JohnDoe the downside of that approach is you have to maintain the ordered list (read: array) of id in one group (read: category) to be in sync with the actual id of resources in that group. – Curious Sam Nov 22 '15 at 8:45

Not the answer you're looking for? Browse other questions tagged or ask your own question.