ドラッグ&ドロップ時のデータベースの操作を考える

ドラッグ&ドロップをした時、裏側のデータベースでは何が起こってドラッグ&ドロップした後のデータが保存されているのかを調べ、実装まで行った。この記事では、ToDoリストの実装を通して、ドラッグ&ドロップをした時のデータベースの操作について書く。

概要

問題点

ドラッグ&ドロップでタスク等の順番を変更しても、リロード時にドラッグ&ドロップの順番変更が保存されていない。

解決策

ドラッグ&ドロップをした際に、フロントエンド側の操作に加えて、バックエンド側のデータベースの操作も行う必要がある。

考え方

1. データベースに順番を管理するカラムを追加

テーブルにindex_numberというカラムを作成する。そしてタスクをデータベースに保存する際、idや内容に加えて、index_numberという数字を持たせる。

2. 新しく作成したカラムにデータを入れる

新しくデータを追加する時、
Case1. テーブルのrowが一つもない場合
index_number = 1024を入れる

Case2. テーブルにrowが一つでもある場合
index_number = 現在のindex_number最大値 + 1024とする。
すると、下記のようなデータベーステーブルが出来上がる

id	todo	index_number
1	work	1024
2	study	2048
3	eat	3072
4	sleep	4096

3. ドラッグ&ドロップを実行、ドラッグ&ドロップした要素のindex_numberを更新

いくつかデータができたら、サーバーを立ち上げドラッグ&ドロップを実行する。
上図で、例えば、ドラッグ&ドロップで、studyをeatとsleepの間に入れたい場合、(3072(eat) + 4096(sleep)) / 2をstudyの新たなindex_numberにする。
(3072 + 4096) / 2 = 3584 <- これがstudy の新しいindex_numberになる。
下記のようにテーブルが更新される

id	todo	index_number
1	work	1024
2	study	3584
3	eat	3072
4	sleep	4096

4. 取り出し、表示する際に、ORDER BYで順番に表示

このテーブルをORDER BY index_numberでクエリを送ることよって、index_numberの昇順でデータを取り出せる。よってドラッグ&ドロップによる順番変更の後、リロードされても順番が保存されていることになる。

5. index_numberが重なった場合

タスクをドラッグ&ドロップした後、そのタスクの上と下にあるタスクのindex_numberの平均値を取る。その平均値の数字がドラッグ&ドロップをしたタスクのindex_numberになる。しかし平均値を取ったとき、2つタスクのindex_numberが重なることがある。
その場合のみ、テーブル全体でindex_numberが小さい順に並べて、順に*1024をしてindex_numberを振り直す必要がある。

実装

使用した言語、ライブラリー

フロントエンド
・JavaScript
・SortableJS

バックエンド
・Node.js
・MySQL

ファイルの構成

1. 必要なnpmをインストール

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

2. ToDoリストのCRUD機能を書く

タスクの編集と削除、一つのデータの取り出しのコードは基本的なCRUD機能付きのToDoリストと変わらないので割愛。

以下より、
list機能(全てのデータの取り出し)、create機能(タスクの追加)、ドラッグ&ドロップ機能(SQLの操作)の順番にコードを書いていく。

3. list機能(全てのデータの取り出し)

基本的には、普通に取り出すだけだが、SQL文が少し違う。

app.js

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

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

一点通常に取り出すのと異なる点は、ORDER BY index_numberでindex_numberの昇順で取り出している点。
こうすることで、ドラッグ&ドロップをしてもデータベース側でどのタスクがどこにあるのかをindex_numberを基に把握することができ、取り出せる。

4. create機能(タスクの追加)

新しいタスクを追加する際には、現在のindex_numberの最大値を取得し、そこに+1024した数字を新しいタスクのindex_numberにする必要がある。そうすることで、新しいタスクがToDoリストの一番下に追加される

app.js

app.post("/add/apis", async (req, res) => {
  // formからの値
  const todo = req.body.todo;

  try {
  // index_numberの最大値の取得し、返す
  // もしテーブルにデータがない場合は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;`
    );
  // 新しいタスクを追加する
  // タスクの内容と、上のクエリで取得した値+1024をした数字を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 });
  }
});

5. ドラッグ&ドロップ機能(SQLの操作)

ここを書くことで、データベースに順番が保存され、ドラッグ&ドロップをしてリロードをしても順番が変わらずに表示される。
ポイントは、
①ドラッグ&ドロップをしたタスクの上と下にあるタスクのindex_numberを取得すること
②①で上または下にタスクがない場合、それぞれindex_numberはNaNにすること
③index_numberが重なった場合は、テーブル全体でORDER BY index_numberをして、昇順に1~順に*1024をしてindex_numberを振り直すこと。

app.js

app.post("/order-todos/:id", async (req, res) => {
  const id = req.params.id;
  // ドラッグ&ドロップしたタスクの上にあるタスクのindex_number
  let prevElIndexNumber = req.body.prevElIndexNumber;
  // ドラッグ&ドロップしたタスクの下にあるタスクのindex_number
  let nextElIndexNumber = req.body.nextElIndexNumber;
  // ドラッグ&ドロップしたタスクのindex_numberを入れる変数
  let currElIndexNumber;

  // prevElIndexNumber === undefined、つまりドラッグ&ドロップしたタスクがToDoリストの一番上になった時、
  // 上のタスクがないので、下のタスクのindex_number - 512をcurrElIndexNumberとする
  if (prevElIndexNumber === undefined) {
    currElIndexNumber = nextElIndexNumber - 512;
    // ドラッグ&ドロップしたタスクがToDoリストの一番下になった時は、
    // 上のタスクのindex_number + 512をcurrElIndexNumberとする
  } else if (nextElIndexNumber === undefined) {
    currElIndexNumber = prevElIndexNumber + 512;
    // ドラッグ&ドロップしたタスクの上下両方にタスクがある場合は、
    // currElIndexNumber = (上のタスクのindex_number + 下のタスクのindex_number)/2
  } else {
    currElIndexNumber = Math.floor((prevElIndexNumber + nextElIndexNumber) / 2);
  }

  try {
    // index_numberが重なった場合の処理
    if (
      currElIndexNumber === prevElIndexNumber ||
      currElIndexNumber === nextElIndexNumber
    ) {
      // index_numberの昇順に1~順に取得(= orderedData)、アップデート
      const orderedData = await util
        .promisify(connection.query)
        .bind(connection)(
        `SELECT *, ROW_NUMBER() OVER (ORDER BY index_number) as orderedData FROM todo;`
      );
      orderedData.forEach(async (element) => {
        await util.promisify(connection.query).bind(connection)(
          `UPDATE todo SET index_number = ${element.orderedData}*1024 where id = ${element.id}`
        );
      });
      return;
    }
    // index_numberが重ならなかった場合は、currElIndexNumberを新しいタスクのindex_numberとして、アップデート
    await util.promisify(connection.query).bind(connection)(
      `UPDATE todo SET index_number = ${currElIndexNumber} where id = ${id}`
    );
    res.end();
  } catch (e) {
    res.status(500).send({ e });
  }
});

少し長くなったが、ざっくり図にすると、

6.フロントエンド側のJS

ロード時にapiをjson形式で取り出して全てのタスクを表示させるところから、ドラッグ&ドロップをした時にhttpリクエストを送信するまでのコードを簡単に説明。

apiをjson形式で取り出して表示

script.js

const wrapper = document.getElementById("wrapper");
window.onload = async () => {
  try {
    // apiデータを全てfetch
    await fetch("http://localhost:3000/list/apis")
      .then((allToDo) => {
        return allToDo.json();
      })
      .then((datas) => {
        datas.results.forEach((data) => {
          const todoEl = document.createElement("div");
          todoEl.classList.add("item");
          const taskId = data.id;
          const text = data.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()が発火する
          todoEl.addEventListener("dragend", () => {
            changePosition(todoEl, taskId);
          });
          wrapper.appendChild(todoEl);
        });
      });
  } catch (e) {
    console.log(e);
  }
};

ドラッグ&ドロップが終わった際のhttpリクエスト処理

上記のコードで、各タスクのドラッグが終わる度に、changePosition()が発火。
changePosition()内で、ドラッグ&ドロップしたタスクの上下にあるタスクのindex_numberを取得、httpリクエストでデータを送信。

script.js

async function changePosition(currEl, currElId) {
  let prevElIndex_Number;
  let nextElIndex_Number;

  try {
    // ドラッグ&ドロップをしたタスクの上にタスクがあればindex_numberを取得
    // なければundefined
    if (currEl.previousSibling !== null) {
      const prevElId = currEl.previousSibling.getAttribute("taskId");

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

    // ドラッグ&ドロップをしたタスクの下にタスクがあればindex_numberを取得
    // なければundefined
    if (currEl.nextSibling != null) {
      const nextElId = currEl.nextSibling.getAttribute("taskId");
      await fetch("http://localhost:3000/read/apis/" + nextElId)
        .then((data) => {
          return data.json();
        })
        .then((json) => {
          nextElIndex_Number = json.results[0].index_number;
        });
    }
    // HTTPリクエスト
     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);
  }
}

まとめ

その他のコード含め、全容はGitHubに。
おそらく他にもドラッグ&ドロップをした後のポジションを保存しておく方法はたくさんあるが、一つの考え方として、対象要素のポジションによってその要素の持つ数字を計算してポジションを保存する方法を書いてみた。