TODOリスト・本棚・プレイリスト

ユーザが好きな順番に並び替えられるTODOリスト・本棚・プレイリストのような、 特定の順番で表示するリストといったものの並び順をデータベースに保存したいケースがある。

これを愚直に実装してしまうと、リストが長くなればなるほど UPDATE すべき行が増えてしまうため、 サーバサイドで実装する場合にはパフォーマンス等も鑑みてひと工夫入れたい。

更新行を減らし、かつ使い勝手が悪くならないような実装方法をメモしておく。

概要

ここでは例としてTODOリストの並び順を保存してみる。 1ユーザにつき1つの順序付きTODOリストを管理できるものとして、テーブルを次のようにする。

キモの部分は SELECT 時に rank のみではなく、 updated_at でもソートすること。 rank の値が同一でも更新日時の新しいものが勝つようにすれば対象要素以外の行の更新は不要になる。 ちなみに、ソートはSQL内でやらなくても良い。

Schema

CREATE TABLE `todos` (
    `todo_id` int(10) unsigned NOT NULL,
    `content` varchar(32) NOT NULL,
    PRIMARY KEY (`todo_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `todo_orders` (
    `user_id` int(10) unsigned NOT NULL,
    `todo_id` int(10) unsigned NOT NULL,
    `rank` int(10) unsigned NOT NULL,
    `created_at` int(10) unsigned NOT NULL,
    `updated_at` int(10) unsigned NOT NULL,
    PRIMARY KEY (`user_id`,`todo_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

要素の追加と順序の入れ替え

ある要素を n 番目にする場合、 rank = n となるように UPSERT する。

INSERT INTO
    todo_orders (user_id, todo_id, rank, created_at, updated_at)
VALUES
    (1, 1, 1, UNIX_TIMESTAMP(), UNIX_TIMESTAMP())
ON DUPLICATE KEY UPDATE
    rank       = VALUES(rank),
    updated_at = VALUES(updated_at);

順序付きでリストを取得する

rank を ASC で、 updated_at を DESC でソートすることによって、 rank の値が同一だった場合でも更新タイミングの新しいものが優先されるので期待した順序で取得できる。

SELECT
    t.todo_id,
    t.content,
    tos.rank,
    tos.updated_at
FROM
    todo_orders tos
INNER JOIN
    todos t
ON
    t.todo_id = tos.todo_id
WHERE
    user_id = 1
ORDER BY
    rank ASC,
    updated_at DESC;

検証

初期のデータセットを元に順序を更新していく。

> SELECT * FROM todos;
+---------+-----------------------------+
| todo_id | content                     |
+---------+-----------------------------+
|       1 | Buy anniversary present     |
|       2 | Send invoices               |
|       3 | Finish homework             |
|       4 | Finalize hotel reservations |
+---------+-----------------------------+
4 rows in set (0.00 sec)

> SELECT * FROM todo_orders;
+---------+---------+------+------------+------------+
| user_id | todo_id | rank | created_at | updated_at |
+---------+---------+------+------------+------------+
|       1 |       1 |    1 | 1393300000 | 1393300000 |
|       1 |       2 |    2 | 1393300000 | 1393300000 |
|       1 |       3 |    3 | 1393300000 | 1393300000 |
|       1 |       4 |    4 | 1393300000 | 1393300000 |
+---------+---------+------+------------+------------+

> SELECT t.todo_id, t.content, tos.rank, tos.updated_at FROM todo_orders tos INNER JOIN todos t ON t.todo_id = tos.todo_id WHERE user_id = 1 ORDER BY rank ASC, updated_at DESC;
+---------+-----------------------------+------+------------+
| todo_id | content                     | rank | updated_at |
+---------+-----------------------------+------+------------+
|       1 | Buy anniversary present     |    1 | 1393300000 |
|       2 | Send invoices               |    2 | 1393300000 |
|       3 | Finish homework             |    3 | 1393300000 |
|       4 | Finalize hotel reservations |    4 | 1393300000 |
+---------+-----------------------------+------+------------+
4 rows in set (0.00 sec)

Finish homework を2番めにした後、Finalize hotel reservations を2番めにすると、

  • Buy anniversary present
  • Finalize hotel reservations
  • Finish homework
  • Send invoices

のような順番になる。

> INSERT INTO todo_orders (user_id, todo_id, rank, created_at, updated_at) VALUES (1, 3, 2, UNIX_TIMESTAMP(), UNIX_TIMESTAMP()) ON DUPLICATE KEY UPDATE rank = VALUES(rank), updated_at = VALUES(updated_at);
Query OK, 2 rows affected (0.55 sec)

> SELECT t.todo_id, t.content, tos.rank, tos.updated_at FROM todo_orders tos INNER JOIN todos t ON t.todo_id = tos.todo_id WHERE user_id = 1 ORDER BY rank ASC, updated_at DESC;
+---------+-----------------------------+------+------------+
| todo_id | content                     | rank | updated_at |
+---------+-----------------------------+------+------------+
|       1 | Buy anniversary present     |    1 | 1393300000 |
|       3 | Finish homework             |    2 | 1393300111 |
|       2 | Send invoices               |    2 | 1393300000 |
|       4 | Finalize hotel reservations |    4 | 1393300000 |
+---------+-----------------------------+------+------------+
4 rows in set (0.02 sec)

> INSERT INTO todo_orders (user_id, todo_id, rank, created_at, updated_at) VALUES (1, 4, 2, UNIX_TIMESTAMP(), UNIX_TIMESTAMP()) ON DUPLICATE KEY UPDATE rank = VALUES(rank), updated_at = VALUES(updated_at);
Query OK, 2 rows affected (0.01 sec)

> SELECT t.todo_id, t.content, tos.rank, tos.updated_at FROM todo_orders tos INNER JOIN todos t ON t.todo_id = tos.todo_id WHERE user_id = 1 ORDER BY rank ASC, updated_at DESC;
+---------+-----------------------------+------+------------+
| todo_id | content                     | rank | updated_at |
+---------+-----------------------------+------+------------+
|       1 | Buy anniversary present     |    1 | 1393300000 |
|       4 | Finalize hotel reservations |    2 | 1393300222 |
|       3 | Finish homework             |    2 | 1393300111 |
|       2 | Send invoices               |    2 | 1393300000 |
+---------+-----------------------------+------+------------+
4 rows in set (0.00 sec)