炊きたてのご飯が食べたい

定時に帰れるっていいね。自宅勤務できるっていいね。子どもと炊きたてのご飯が食べられる。アクトインディでは積極的にエンジニアを募集中です。

ユーザー側で任意にアイテムの表示順を更新する（sqlite3）


  • アイテムの並び替えの画面

ユーザーが好きなようにアイテムの並び替えを行う際、どのように作成するのが良いのか悩みました。単純にアイテム毎に並び順のカラムをつけ、変更がある度に全てのアイテムの並び順の値を1から番号を割り振るのは、アイテム数が多くなって来たときに更新対象が多くなってしまうなぁと思い、全てのアイテムの更新を都度必要としない方法を考えてみました。サンプルソースはgithubで公開しています。objective-cです。

サンプルソース

https://github.com/takanamishi/items-sorting

1.テーブル情報

1.複数のアイテムと並び順を持つcategoriesテーブル

id category_name category_ord del_flg create_at update_at
1 Part1 ファントムブラッド 1 0 2014-12-30 20:57:24 2014-12-30 20:57:24
2 Part2 戦闘潮流 2 0 2014-12-30 20:57:24 2014-12-30 20:57:24
3 Part3 スターダストクルセイダース 3 0 2014-12-30 20:57:24 2014-12-30 20:57:24
4 Part4 ダイヤモンドは砕けない 4 0 2014-12-30 20:57:24 2014-12-30 20:57:24
5 Part5 黄金の風 5 0 2014-12-30 20:57:24 2014-12-30 20:57:24
6 Part6 ストーンオーシャン 6 0 2014-12-30 20:57:24 2014-12-30 20:57:24
7 Part7 スティール・ボール・ラン 7 0 2014-12-30 20:57:24 2014-12-30 20:57:24
  • id:カテゴリーID（PRIMARY KEY AUTOINCREMENT）
  • category_name:カテゴリー名
  • category_ord:カテゴリーの並び順（NOT NULL UNIQUE）
  • del_flg:削除フラグ
  • create_at:作成日
  • update_at:更新日 上記テーブルを作成するSQL文は下記です。

[sql] CREATE TABLE categories (id INTEGER PRIMARY KEY AUTOINCREMENT, category_name TEXT NOT NULL UNIQUE, category_ord INTEGER NOT NULL UNIQUE, del_flg INTEGER NOT NULL DEFAULT 0, created_at TIMESTAMP NOT NULL DEFAULT (DATETIME('now', 'localtime')), updated_at TIMESTAMP NOT NULL DEFAULT (DATETIME('now', 'localtime'))); [/sql]

カテゴリーの並び順のカラムにはUNIQUE制約を設定しています。

2 .考えた並び替え方法

単純にアイテム毎に並び順のカラムをつけ、変更がある度に全てのアイテムの並び順の値を1から番号を割り振るのは非効率なので、更新対象のアイテムを必要な分だけ更新するようにできないか考えました。アイテムの並び順は決して綺麗な形で値を持たなくても良いので（「1,2,4…」と抜け番があっても、並び順が担保されれば良い）、そこに着目した方法になります。ポイントは以下になります。

  1. 移動先のアイテムの並び順番号の前後の値が抜け番になっているかチェック
  2. 抜け番があれば、他のアイテムの並び順番号は更新せず、移動したアイテムの並び順番号のみ更新
  3. 空いていない場合は、移動先のアイテムの並び順番号以降を更新 以下、サンプルを用いながら解説していきます。

並び順を後ろにずらす場合

例）「Part3 スターダストクルセイダース」を「Part5 黄金の風」の後ろに移動

「Part5 黄金の風」のcategory_ord + 1（6）のIDが空いているかチェック

空いている場合

  1. 「Part3 スターダストクルセイダース」のcategory_ordの値を6に更新 空いていない場合

  2. 「Part6 ストーンオーシャン」「Part7 スティール・ボール・ラン」のcategory_ordの値をそれぞれ+1して更新

  3. 「Part3 スターダストクルセイダース」のcategory_ordの値を6に更新 図1のテーブル状態の状態で、今回の更新を行うと、category_ordの6はPart6 ストーンオーシャンで利用しているので、「空いていない場合」に該当し、更新後のテーブルは下記の状態になります。

id | category_name | category_ord | del_flg | create_at | update_at
-- | ------------------- | ------------ | ------- | ------------------- | ------------------- 1 | Part1 ファントムブラッド | 1 | 0 | 2014-12-30 21:12:36 | 2014-12-30 21:12:36 2 | Part2 戦闘潮流 | 2 | 0 | 2014-12-30 21:12:36 | 2014-12-30 21:12:36 3 | Part3 スターダストクルセイダース | 6 | 0 | 2014-12-30 21:12:36 | 2014-12-30 21:20:12 4 | Part4 ダイヤモンドは砕けない | 4 | 0 | 2014-12-30 21:12:36 | 2014-12-30 21:12:36 5 | Part5 黄金の風 | 5 | 0 | 2014-12-30 21:12:36 | 2014-12-30 21:12:36 6 | Part6 ストーンオーシャン | 7 | 0 | 2014-12-30 21:12:36 | 2014-12-30 21:20:12 7 | Part7 スティール・ボール・ラン | 8 | 0 | 2014-12-30 21:12:36 | 2014-12-30 21:20:12 画面はこのように表示されます。

並び替え実施後の画面

「空いている場合」はそのまま空いているcategory_ordの値で更新が行われます。

並び順を前にずらす場合

並び順を前にずらす場合も似たような形です。説明を分かりやすくする為、テーブルの状態は最初の状態で説明します。テーブルの状態は下記です。

id category_name category_ord del_flg create_at update_at
1 Part1 ファントムブラッド 1 0 2014-12-30 20:57:24 2014-12-30 20:57:24
2 Part2 戦闘潮流 2 0 2014-12-30 20:57:24 2014-12-30 20:57:24
3 Part3 スターダストクルセイダース 3 0 2014-12-30 20:57:24 2014-12-30 20:57:24
4 Part4 ダイヤモンドは砕けない 4 0 2014-12-30 20:57:24 2014-12-30 20:57:24
5 Part5 黄金の風 5 0 2014-12-30 20:57:24 2014-12-30 20:57:24
6 Part6 ストーンオーシャン 6 0 2014-12-30 20:57:24 2014-12-30 20:57:24
7 Part7 スティール・ボール・ラン 7 0 2014-12-30 20:57:24 2014-12-30 20:57:24
例）「Part5 黄金の風」を「Part3 スターダストクルセイダース」の前に移動

「Part3 スターダストクルセイダース」のcategory_ord - 1（2）のIDが空いているかチェック

空いている場合

  1. 「Part5 黄金の風」のcategory_ordの値を2に更新 空いていない場合

  2. 「Part3 スターダストクルセイダース」「Part4 ダイヤモンドは砕けない」「Part5 黄金の風」「Part6 ストーンオーシャン」「Part7 スティール・ボール・ラン」のcategory_ordの値をそれぞれ+1して更新

  3. 「Part5 黄金の風」のcategory_ordの値を2に更新 今回の並び替えの場合、category_ordの2はPart2 戦闘潮流で利用しているので、「空いていない場合」に該当し、更新後のテーブルは下記の状態になります。

id | category_name | category_ord | del_flg | create_at | update_at
-- | ------------------- | ------------ | ------- | ------------------- | ------------------- 1 | Part1 ファントムブラッド | 1 | 0 | 2015-01-09 07:13:59 | 2015-01-09 07:13:59 2 | Part2 戦闘潮流 | 2 | 0 | 2015-01-09 07:13:59 | 2015-01-09 07:13:59 3 | Part3 スターダストクルセイダース | 4 | 0 | 2015-01-09 07:13:59 | 2015-01-10 08:19:57 4 | Part4 ダイヤモンドは砕けない | 5 | 0 | 2015-01-09 07:13:59 | 2015-01-10 08:19:57 5 | Part5 黄金の風 | 3 | 0 | 2015-01-09 07:13:59 | 2015-01-10 08:19:57 6 | Part6 ストーンオーシャン | 7 | 0 | 2015-01-09 07:13:59 | 2015-01-10 08:19:57 7 | Part7 スティール・ボール・ラン | 8 | 0 | 2015-01-09 07:13:59 | 2015-01-10 08:19:57 「空いている場合」はそのまま空いているcategory_ordの値で更新が行われます。

画面はこのように表示されます。

並び替え実施後の画面2

追記

並び替えを何度か実施した場合、category_ordに抜け番が生じてきます。その際、アイテムを全て更新する必要がないよう、抜け番のcategory_ordの値を取得し、更新するアイテムを特定するようにしています。

例）テーブルの状態

id | category_name | category_ord | del_flg | create_at | update_at
-- | ------------------- | ------------ | ------- | ------------------- | ------------------- 1 | Part1 ファントムブラッド | 1 | 0 | 2014-12-30 21:12:36 | 2014-12-30 21:12:36 2 | Part2 戦闘潮流 | 2 | 0 | 2014-12-30 21:12:36 | 2014-12-30 21:12:36 3 | Part3 スターダストクルセイダース | 3 | 0 | 2014-12-30 21:12:36 | 2014-12-30 21:20:12 4 | Part4 ダイヤモンドは砕けない | 4 | 0 | 2014-12-30 21:12:36 | 2014-12-30 21:12:36 5 | Part5 黄金の風 | 5 | 0 | 2014-12-30 21:12:36 | 2014-12-30 21:12:36 6 | Part6 ストーンオーシャン | 7 | 0 | 2014-12-30 21:12:36 | 2014-12-30 21:20:12 7 | Part7 スティール・ボール・ラン | 8 | 0 | 2014-12-30 21:12:36 | 2014-12-30 21:20:12 「Part1 ファントムブラッド」を「Part3 スターダストクルセイダース」の後ろに移動する場合、下記SQLを実行し、抜け番となるcategory_ordの値を取得します。

[sql] SELECT MIN(category_ord + 1) AS missing_number FROM categories c WHERE category_ord >= 3 AND NOT EXISTS(SELECT 1 FROM categories c2 WHERE c.category_ord + 1 = c2.category_ord) [/sql]

今回のケースでは、「Part3 スターダストクルセイダース」のcategory_ordの3以上の数字で抜け番となっている「6」の値を取得し、更新対象となるアイテムは「Part3 スターダストクルセイダース」「Part4 ダイヤモンドは砕けない」「Part5 黄金の風」となります。

この並び替えの問題点

更新するアイテムを減らすことを目的に考えましたが、更新前の事前のチェックやらで、更新までに確認用のSQLの発行が増えます。

  1. 移動先のcategory_ordの値が空いているか確認するSELECT文
  2. 値が空いていなかった場合、更新対象のアイテムを絞り込むためにcategory_ordの抜け番を取得するSELECT文 アイテムが少ない時は逆にパフォーマンスに与える影響は大きくなるかと思いますが、アイテムが増えれば増えるほど効果はじわじわと効いてくると思います。

サンプルソース

https://github.com/takanamishi/items-sorting