How to Update the Database After a Drag-and-Drop Operation

I researched what happens in the database behind the scenes when drag & drop is done and what happens to the data after the drag & drop.
In this post, I will write about the database operations when dragging and dropping through the implementation of a to-do list.

This is a demo for this post

You will see that the order of tasks will not change even after reloading.

Overview

Problem

Even after changing the order of tasks by drag & drop, the drag & drop order change is not saved when reloading.

Solution

When you do drag and drop, you need to operate the database on the backend side in addition to the frontend side.

How It Works

Step 1. Add a column to the database to control the order

Create a column called index_number in the table. Then, when the task is stored in the database, it will have the number index_number in addition to the id and content.

Step 2. Fill the newly created column with data

When adding the new data,
Case1. if there are no rows in the table,
Insert index_number = 1024

Case2. if the table has at least one row,
Set index_number = current maximum index_number + 1024

This will lead you to create a database table as shown below:

Step 3. Perform drag and drop, update index_number of dragged and dropped element

Once you make some data in the table, start the server up and perform drag & drop. In the above figure, for example, if you want to put “study” between “eat” and “sleep” by dragging and dropping,
set (3072(eat) + 4096(sleep)) / 2
as the new index_number for “study”.

(3072 + 4096) / 2 = 3584 ← This will be the new index_number for “study”. The table will be updated as follows:

Step 4. Use ORDER BY when retrieving and displaying the table

By querying this table with “ORDER BY index_number”, you can retrieve the data in ascending order by index_number. Thus, even if the order is changed by drag-and-drop and then reloaded, the order is preserved.

Step 5. If index_number overlaps

After dragging and dropping a task, the task’s index_number is calculated by taking the average of index_numbers of the task above and the task below.

So sometimes, the index_number of two tasks may overlap.

Only in this case, it is necessary to arrange the entire table in order of decreasing index_number, and then reassign the index_number by *1024.

Implementation

Languages and libraries used

Frontend
・JavaScript
・SortableJS
Backend
・Node.js
・MySQL

File Structure

Step1. Install the necessary npm

npm i express mysql2 path body-parser util dotenv --save
npm i nodemon --save-dev

Step2. Write a CRUD function for the To-Do list

The code for editing and deleting tasks and retrieving a single piece of data is the same as the regular ToDo list with CRUD function, so I’ll skip it.

From the following steps,
I will write the code for:
the list function (retrieving all data),
the create function (adding a task), and
the drag & drop function (SQL operations) in that order.

Step3. List function (retrieving all data)

Basically, it just extracts the data as usual, but the SQL statement is a little different from the regular ToDo list.

app.get("/list/apis", async (req, res) => {
  try {
    const results = await util.promisify(connection.query).bind(connection)(
      "SELECT * FROM todo ORDER BY `index_number`" // Use ORDER BY `index_number`
    );

    res.json({ results });
  } catch (e) {
    res.status(500).send({ e });
  }
});

One point that is different from the usual way of retrieving tasks is that the tasks are retrieved in ascending order of index_number in ORDER BY index_number.
In this way, even if you drag and drop, the database will know the order of all tasks based on the index_number, and you can retrieve data correctly.

Step4. Create function (adding a task)

When you add a new task, you need to get the maximum value of the current index_number and make the number +1024 to the index_number of the new task.
By doing so, the new task will be added to the bottom of the To-Do list.

app.post("/add-todos", async (req, res) => {
  // value of todo task
  const todo = req.body.todo;

  try {
  // Get and return the maximum value of `index_number`
  // if there is no data in the table, return 0
    const results = await util.promisify(connection.query).bind(connection)(
      `SELECT IFNULL((SELECT index_number FROM todo ORDER BY index_number DESC LIMIT 1) ,0) as max_index_number;`
    );
  // Add a new task
  // Put the contents of the task and the value obtained in the above query + 1024 into VALUES
    await util.promisify(connection.query).bind(connection)(
      `INSERT INTO todo(todo, index_number) VALUES('${todo}', ${results[0].max_index_number}+1024)`
    );
    res.redirect("/");
  } catch (e) {
    res.status(500).send({ e });
  }
});

Step5. Drag & drop function (MySQL operations)

By writing here, the order will be saved in the database, and the order will remain the same even after dragging and dropping and reloading.

The points are:

1. Get the index_number of the task above and below the task you dragged and dropped.

2. If there is no task above the dragged and dropped task, index_number cannot be obtained. So the index_number of the task above the task you dragged and dropped will be undefined.

3. Same as (2), if there is no task below the dragged and dropped task, index_number cannot be obtained. So the index_number of the task below the task you dragged and dropped will be undefined.

4. If the index_number overlaps, ORDER BY index_number for the entire table, and reassign the index_number in ascending order.
   

app.post("/order-todos/:id", async (req, res) => {
  const id = req.params.id;
  // index_number of the task above the dragged and dropped task
  let prevElIndexNumber = req.body.prevElIndexNumber;
  // index_number of the task under the dragged and dropped task
  let nextElIndexNumber = req.body.nextElIndexNumber;
  // a variable containing the index_number of the dragged and dropped task
  let currElIndexNumber;

  // prevElIndexNumber === undefined, this is happended when the drag-and-drop task is at the top of the to-do list.
  // Since there is no upper task, set the index_number of the lower task - 512 as the currElIndexNumber
  if (prevElIndexNumber === undefined) {
    currElIndexNumber = nextElIndexNumber - 512;
  // nextElIndexNumber === undefined, this is happended when the dragged-and-dropped task is at the bottom of the to-do list
  // Set the index_number of the task above + 512 as the currElIndexNumber
  } else if (nextElIndexNumber === undefined) {
    currElIndexNumber = prevElIndexNumber + 512;
  // If there are tasks both above and below the dragged-and-dropped task, then
  // currElIndexNumber = (index_number of the top task + index_number of the bottom task)/2
  } else {
    currElIndexNumber = Math.floor((prevElIndexNumber + nextElIndexNumber) / 2);
  }

    try {
    // Update currElIndexNumber as the index_number of the new task
    await util.promisify(connection.query).bind(connection)(
      `UPDATE todo SET index_number = ${currElIndexNumber} where id = ${id}`
    );

    // When index_number overlaps
    if (
      Math.abs(currElIndexNumber - prevElIndexNumber) <= 1 ||
      Math.abs(currElIndexNumber - nextElIndexNumber) <= 1
    ) {
      // Get index_number in ascending order from 1~ (= orderedData), then update the table
      const orderedData = await util
        .promisify(connection.query)
        .bind(connection)(
        `SELECT *, ROW_NUMBER() OVER (ORDER BY index_number) as orderedData FROM todo;`
      );
      await Promise.all(
        orderedData.map(async (element) => {
          await util.promisify(connection.query).bind(connection)(
            `UPDATE todo SET index_number = ${element.orderedData}*1024 where id = ${element.id}`
          );
        })
      );
    }
    res.end();
  } catch (e) {
    res.status(500).send({ e });
  }
});

It’s a bit long, but here’s a quick diagram

Step6. JavaScript on the front-end side

Here is a simple explanation of the code from extracting api in json format on load to display all tasks, to sending http request when drag and drop is done.

Extract and display api in json format

// fetch api and display all stored datas
const wrapper = document.getElementById("wrapper");
window.onload = async () => {
  try {
    // fetch all data of todo
    await fetch("http://localhost:3000/list-todos")
      .then(async (allToDo) => {
        return await allToDo.json();
      })
      .then((datas) => {
        datas.results.forEach((el) => {
          const todoEl = document.createElement("div");
          todoEl.classList.add("item");
          const taskId = el.id;
          const text = el.todo;

          todoEl.setAttribute("taskId", taskId);
          todoEl.innerHTML = `<span class="txt" onClick="startEditToDo(this, ${taskId})">${text}</span><i class="trash fa fa-trash" onClick="deleteToDo(this.parentNode, ${taskId})"></i><i class="icon fa fa-bars"></i>`;
          // changePostion() after dragend
          todoEl.addEventListener("dragend", () => {
            changePosition(todoEl, taskId);
          });
          wrapper.appendChild(todoEl);
        });
      });
  } catch (e) {
    console.log(e);
  }
};

Http request processing when the drag & drop is done

In the above code, changePosition() fires every time the dragging of each task is completed.
In changePosition(), the index_number of the task above and below the dragged and dropped task is obtained, and the data is sent by http request.

async function changePosition(currEl, currElId) {
  let prevElIndexNumber;
  let nextElIndexNumber;

  try {
    // Get index_number if there is a task on top of the dragged and dropped task
    // if not, undefined
    if (currEl.previousSibling !== null) {
      const prevElId = currEl.previousSibling.getAttribute("taskId");

      await fetch("http://localhost:3000/read-todos/" + prevElId)
        .then(async (data) => {
          return await data.json();
        })
        .then((json) => {
          prevElIndexNumber = json.results[0].index_number;
        });
    }

    // Get index_number if there is a task under the drag & drop task
    // if not, undefined
    if (currEl.nextSibling != null) {
      const nextElId = currEl.nextSibling.getAttribute("taskId");
      await fetch("http://localhost:3000/read-todos/" + nextElId)
        .then(async (data) => {
          return await data.json();
        })
        .then((json) => {
          nextElIndexNumber = json.results[0].index_number;
        });
    }

    // HTTP Request
    const updateUrl = "http://localhost:3000/order-todos/" + currElId;

    await fetch(updateUrl, {
      method: "POST",
      headers: {
        "Content-type": "application/json",
      },
      body: JSON.stringify({ prevElIndexNumber, nextElIndexNumber }),
    });
  } catch (e) {
    console.log(e);
  }
}

Summary

The whole thing, including the rest of the code, is on GitHub.

There are probably many other ways to save the position after dragging and dropping, but as one way to think about it, I wrote a way to save the position by calculating the number the element has depending on the position of the target element.

Comments

[Arta Mohamadi]

Hi Sho-ki!!
Thanks a lot for this article i really need this one.
but there is point in github source =>app.js there is no routes named add-todos and instead
for add there is routes name "add/apis" and change this one to add-todos and now its work. :D

[Shoki Ishii]

Hi Arta,
I really appreciate your kindness! That was a big mistake.
I change the routes name "add/apis" to "add-todos".

[Arta Mohamadi]

Tnx bro 🌻😀

[g4rry420]

Hi Sho-ki,
Is there a particular reason that you used the 1024 for the index value and then also increment it by 1024 ? Why didn't you used index values such as 0,1,2,3,4, etcetera.

[Shoki Ishii]

Hi,
There is no major reason for this.
But see 'Step 5. If index_number overlaps in How it works'.
In the case using 1,2,3,4..., then index_number will immediately overlap with another index_number. This may result in slower processing, so I use a relatively large number, 1024, 2048, 3072..., which is not immediately overlap with another index_number.