How do I architect reordering in database?

Question

I'm building a todo list app and using react-beautiful-dnd on the frontend to allow users to reorder tasks. I'm trying to work out how to persist changes to the database when a user reorders items.

With react-beautiful-dnd, when a reorder occurs, there's an onDragEnd callback which provides me with the following information:

{
  "draggableId": "ckfh7msol0005twuz82i2k7oo",
  "type": "DEFAULT",
  "source": {
    "index": 0,
    "droppableId": "droppable"
  },
  "reason": "DROP",
  "mode": "FLUID",
  "destination": {
    "droppableId": "droppable",
    "index": 1
  },
  "combine": null
}

I've only seen examples that show how to persist the change in React state, which is just a case duplicating the array of items, reordering based on start and end indexes and setting the state as the result of that, something like this:

const onDragEnd = (dropResult: DropResult) => {
  const { destination, source } = dropResult

  if (!destination) {
    return
  }

  if (destination.droppableId === source.droppableId && destination.index === source.index) {
    return
  }

  const result = Array.from(tasks)
  const [removed] = result.splice(source.index, 1)
  result.splice(destination.index, 0, removed)
  const reorderedTasks = result.splice(destination.index, 0, removed)

  setTasks(reorderedTasks)
}

The todo items are coming from a call to the server via Apollo Client, so I guess what I'd want to do, rather than setting the state with the result, is send a mutation to reorder the items in the database and the refresh the client with the updated data.

I'm not sure what the best way to do this is. I've seen some suggestions around having e.g. an integer order property on each todo item and when a reorder occurs, adjust the order numbers of each todo item until everything is in the right numerical order.

Another approach I've come across is to use e.g. a floating point order property on each item, and then doing the math based on the order of the item above and below the target position so the the order is in-between.

Basically I'm wondering which route I should go down and how to implement it in the cleanest way.

I'm using Prisma and graphql-yoga on the backend with a sqlite database.

How do I architect reordering in database?

0 Answers0