質問: booksというテーブルがあり、positionというカラムを持たせ、その数値が小さい順にsortして、並べるという処理をする場合に、例えば既に10個レコードが存在し、上からpositionカラムには1,2,3...10という値をもたせてるとします。 新たにレコードを追加する場合に、この新レコードを2番目に表示させたい、と考えた場合、positionに2を入れ、2追加する前に2以上の数値をもっていたレコードはこの場合、ひとつずつ手動でづらしていかなければいけない仕様になってしまっているのですが、何か良い方法はないでしょうか? 開発環境は rails,mysqlを仕様しています。
回答: サンプルデータ ======= !イメージ説明 ケース1 ==== 「え」を「あ」の次に移動するケースを考えます。 「あ」→「え」→「い」→「う」→「お」の順に変更されるケースです。 このとき、最終的に以下のようなデータに変更したいわけですね。 | id | item | orderNumber | | --- | --- | --- | | 1 | あ | 1 | | 4 | *え* | *2* | | 2 | *い* | *3* | | 3 | *う* | *4* | | 5 | お | 5 | 変更対象のレコード「え」の表示順が「4」→「2」に変更されたとき、「2〜3」の表示順を持っているデータが +1 されることがわかります。 ケース2 ==== 今度は、以下の状態から、 | id | item | orderNumber | | --- | --- | --- | | 1 | あ | 1 | | 2 | い | 2 | | 3 | う | 3 | | 4 | え | 4 | | 5 | お | 5 | 「い」を「お」の前に移動するケースを考えます。変更後は、以下のようになります。 | id | item | orderNumber | | --- | --- | --- | | 1 | あ | 1 | | 3 | *う* | *2* | | 4 | *え* | *3* | | 2 | *い* | *4* | | 5 | お | 5 | 「い」の表示順が「2」から「4」に変更されるとき、「3〜4」の表示順を持つレコードの表示順が -1 されます。 まとめ === a > b のとき(ケース1) 対象のレコードの表示順を `a` から `b` に変更されるとき、 ``` UPDATE sample SET orderNumber = orderNumber + 1 WHERE orderNumber BETWEEN b and a AND orderNumber != a; UPDATE sample SET orderNumber = b WHERE id = :id ``` a < b のとき(ケース2) 対象のレコードの表示順を `a` から `b` に変更されるとき、 ``` UPDATE sample SET orderNumber = orderNumber - 1 WHERE orderNumber BETWEEN a and b AND orderNumber != a; UPDATE sample SET orderNumber = b WHERE id = :id ```