並び替えを1クエリで保存する（unnest WITH ORDINALITY）

ドラッグ&ドロップで並べ替えたタグの順序を保存したい。ID の配列 ['c', 'a', 'b'] を受け取って、order 列を 1, 2, 3 と書き込むだけ——のはずが、updateMany でも update_all でも書けない。

なぜ書けないのか、Postgres の unnest(...) WITH ORDINALITY で 1 クエリにまとめる方法、Prisma と Rails それぞれの書き方をまとめる。

結論

• updateMany / update_all は「1 つの値を複数行に配る」もの。並び替えで欲しいのは「行ごとに違う値」なので使えない。
• 解き方は「ID と順番の対応表を作って join し、固定値ではなく相手の列を代入する」。unnest(配列) WITH ORDINALITY がその対応表を作ってくれる。
• 配列を渡すだけなのでバインドパラメータは 1 個。件数が増えても SQL 文は変わらない。文字列連結が要らないのでインジェクションの余地もない。
• ただし 1 文にまとめても UNIQUE (workspace_id, order) は回避できない。制約があるなら DEFERRABLE INITIALLY DEFERRED が要る（後述）。

件数が数十件なら、素直に N 回 UPDATE でも実用上は困らない。1 文にする主な動機は往復回数より「書き方が固定される」ことにある。

そもそも UPDATE 1文では書けない

題材はブログのタグ。ワークスペースごとにタグを持ち、order 列で表示順を決めている。

普通の UPDATE ができること

UPDATE tags SET "order" = 1;

    name    | order
------------+-------
 Postgres   |     1
 Rails      |     1     ← 全部 1
 TypeScript |     1

SET の右辺が固定値なので、当然どの行も同じ値になる。1 つの値を複数行に配るのが UPDATE ... SET 列 = 値 であり、Prisma の updateMany も Rails の update_all もこれのラッパーでしかない。

並び替えで欲しいもの

ユーザーが Postgres, Rails, TypeScript の順にドラッグしたとする。入れたい値は行ごとに違う。

Postgres   → 1
Rails      → 2
TypeScript → 3

SET "order" = ??? の ??? に書ける値は 1 つしかないので、素の UPDATE では表現できない。これが「行ごとに違う値へ一括更新」という言い方の意味で、updateMany / update_all が使えない理由もここにある。

解き方：ID と順番の対応表を作って join する

固定値を代入できないなら、代入元をテーブルにしてしまえばいい。

欲しいのはこういう 2 列の表で、これさえあれば SET "order" = c.new_order と「相手の列」を代入できる。

                tag_id                | new_order
--------------------------------------+-----------
 33333333-3333-3333-3333-333333333333 |         1
 11111111-1111-1111-1111-111111111111 |         2
 22222222-2222-2222-2222-222222222222 |         3

この表を作るのが unnest(配列) WITH ORDINALITY である。

SELECT * FROM unnest(ARRAY[
  '33333333-3333-3333-3333-333333333333',
  '11111111-1111-1111-1111-111111111111',
  '22222222-2222-2222-2222-222222222222'
]::uuid[]) WITH ORDINALITY AS c(tag_id, new_order);

• unnest(配列) … 配列を 1 行 1 要素の表に開く
• WITH ORDINALITY … 何番目の要素かを列として付け足す
• AS c(tag_id, new_order) … 2 列に名前を付ける

配列の並び順がそのまま欲しい順序なので、WITH ORDINALITY を付けるだけで対応表が完成する。順序を別の配列で送る必要がない。

flowchart LR
    A["ID の配列<br/>['3333…', '1111…', '2222…']"] -->|"unnest + WITH ORDINALITY"| B["対応表 c<br/>tag_id / new_order"]
    B -->|"tags.tag_id = c.tag_id で join"| C["tags テーブル"]
    C -->|"SET order = c.new_order"| D["並び替え完了"]

完成形

UPDATE tags
SET "order" = c.new_order
FROM unnest(ARRAY[
  '33333333-3333-3333-3333-333333333333',
  '11111111-1111-1111-1111-111111111111',
  '22222222-2222-2222-2222-222222222222'
]::uuid[]) WITH ORDINALITY AS c(tag_id, new_order)
WHERE tags.tag_id = c.tag_id
  AND tags.workspace_id = 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa'::uuid;

    name    | order
------------+-------
 Postgres   |     1
 Rails      |     2     ← 行ごとに違う値が入った
 TypeScript |     3

UPDATE ... FROM は「他のテーブルと join しながら更新する」Postgres の構文で、join 相手が実テーブルではなく unnest の結果になっているだけ、と読める。

なぜ VALUES や CASE ではないのか

同じことは VALUES や CASE WHEN でも書ける。

-- VALUES 版
UPDATE tags SET "order" = c.new_order
FROM (VALUES ('3333…'::uuid, 1), ('1111…'::uuid, 2)) AS c(tag_id, new_order)
WHERE tags.tag_id = c.tag_id;

-- CASE 版
UPDATE tags SET "order" = CASE tag_id
  WHEN '3333…'::uuid THEN 1
  WHEN '1111…'::uuid THEN 2
END WHERE tag_id IN ('3333…', '1111…');

どちらも件数分のプレースホルダを文字列連結で組み立てる必要がある。件数が可変なので SQL 文自体が動的になり、Prisma なら $executeRawUnsafe に落ちるか、自前でプレースホルダを生成することになる。

unnest なら件数に関係なくパラメータは配列 1 個で、SQL 文は固定のまま。ここが一番の利点。

Prisma で書く

await tx.$executeRaw`
  UPDATE tags
  SET "order" = c.new_order
  FROM unnest(${tagIds}::uuid[]) WITH ORDINALITY AS c(tag_id, new_order)
  WHERE tags.tag_id = c.tag_id
    AND tags.workspace_id = ${workspaceId}::uuid
`;

$executeRaw はタグ付きテンプレートなので、${} は文字列展開ではなくバインドパラメータになる。ID を直接埋めているように見えてインジェクションにはならない。

文字列を受け取る $executeRawUnsafe は別物。名前のとおり展開されるので、可変長の値を扱うときは $executeRaw + 配列を選ぶ。

::uuid[] のキャストは必須

Prisma は JS の string[] を text[] として送る。一方 tags.tag_id は uuid 型で、Postgres は両者を直接比較できない。

SELECT * FROM tags
JOIN unnest(ARRAY['1111…']) AS c(tag_id) ON tags.tag_id = c.tag_id;

ERROR:  operator does not exist: uuid = text
HINT:  No operator matches the given name and argument types.

キャストを付けると型が揃うだけでなく、不正な文字列を Postgres 側でも弾ける。

SELECT * FROM unnest(ARRAY['not-a-uuid']::uuid[]);

ERROR:  invalid input syntax for type uuid: "not-a-uuid"

N 回 update にしない理由

Prisma だけで書くなら update を N 回呼ぶことになる。

for (const [index, tagId] of tagIds.entries()) {
  await tx.tag.update({ where: { tag_id: tagId }, data: { order: index + 1 } });
}

トランザクション内のクライアントは単一コネクションに固定されるため、この N 回は並列化できず素直に N 往復になる。

sequenceDiagram
    participant App as アプリ
    participant DB as Postgres
    Note over App,DB: N 回 update（タグ20件なら20往復）
    App->>DB: UPDATE ... WHERE tag_id = 1
    DB-->>App: OK
    App->>DB: UPDATE ... WHERE tag_id = 2
    DB-->>App: OK
    App->>DB: ...（あと18回）
    DB-->>App: OK
    Note over App,DB: unnest なら1往復
    App->>DB: UPDATE ... FROM unnest(配列)
    DB-->>App: UPDATE 20

とはいえタグは 1 ワークスペースあたり数件〜数十件なので、性能上の必然があるわけではない。1 文にする実利は「件数によらず処理時間が読める」「並び替えロジックが SQL 1 箇所に閉じる」あたりにある。

Rails で書く

1. 素直にループ（小規模ならこれで十分）

Tag.transaction do
  tag_ids.each_with_index do |id, index|
    Tag.where(id: id, workspace_id: workspace_id)
       .update_all(position: index + 1)
  end
end

update_all も「マッチした全行に同じ値」しか入らないので、行ごとに違う値ならループするしかない。N クエリ発行される。

2. upsert_all（Rails 6+ / Postgres）

1 文で行ごとの値を入れられる、Rails 標準の唯一の手段。

Tag.upsert_all(
  tag_ids.each_with_index.map { |id, i| { id: id, position: i + 1 } },
  unique_by: :id
)

中身は INSERT ... ON CONFLICT DO UPDATE なので、NOT NULL でデフォルトの無い列（name など）も全部渡さないと落ちる。「順序だけ更新したい」用途には噛み合わせが悪く、バリデーションもコールバックも飛ぶ。

3. 生 SQL（上と同じ形）

sql = <<~SQL.squish
  UPDATE tags
  SET position = c.position
  FROM unnest(ARRAY[:tag_ids]::uuid[]) WITH ORDINALITY AS c(tag_id, position)
  WHERE tags.id = c.tag_id
    AND tags.workspace_id = :workspace_id
SQL

Tag.connection.exec_update(
  Tag.sanitize_sql_array([sql, tag_ids: tag_ids, workspace_id: workspace_id])
)

sanitize_sql_array が Prisma のタグ付きテンプレートに相当する。Ruby の配列を渡すとカンマ区切りに展開されるので ARRAY[:tag_ids] が組み立つ。

Rails ならではの選択肢：acts_as_list

そもそも「並び順」自体を gem に任せるのが Rails の伝統。

class Tag < ApplicationRecord
  acts_as_list column: :position, scope: :workspace
end

tag.insert_at(3)   # 前後を自動でシフト

作成時の末尾追加（手で書くと max + 1）も面倒を見てくれる。ただし内部では前後の行を個別にずらすのでクエリ数は増える。「全順序をまとめて置き換える」タイプの API とは相性が良くない。

列名は order にしない

Rails では order が ActiveRecord のクエリメソッドと衝突するため、慣習的に position を使う。上の例が position なのはそのため。Prisma では tags."order" とクォートすれば済むので、この問題は表面化しない。

なお SQL では order は予約語なので、クォートを忘れるとそもそも構文エラーになる。

SELECT order FROM tags;

ERROR:  syntax error at or near "FROM"

比較

数十件までなら 1 番で十分。ORM の枠内で済ませたいなら 2 番、順序だけを素直に更新したいなら 3 番。

ハマりどころ

1 文にまとめても一意制約は回避できない

「1 文なら中間状態が見えないから UNIQUE (workspace_id, order) があっても通る」と思いがちだが、通らない。

ALTER TABLE tags ADD CONSTRAINT tags_workspace_order_key UNIQUE (workspace_id, "order");

UPDATE tags SET "order" = c.new_order
FROM unnest(ARRAY['1111…', '3333…', '2222…']::uuid[]) WITH ORDINALITY AS c(tag_id, new_order)
WHERE tags.tag_id = c.tag_id;

ERROR:  duplicate key value violates unique constraint "tags_workspace_order_key"
DETAIL:  Key (workspace_id, "order")=(aaaa…, 1) already exists.

Postgres の一意制約は既定で immediate（行を書くたびに評価）なので、1 文の中でも順次衝突する。回避するには制約自体を遅延可能にする。

ALTER TABLE tags ADD CONSTRAINT tags_workspace_order_key
  UNIQUE (workspace_id, "order") DEFERRABLE INITIALLY DEFERRED;

これでコミット時まで評価が遅れるので、1 文の UPDATE も、トランザクションで包んだ N 回の UPDATE も通るようになる。「1 文にする」ことと「制約を遅延させる」ことは別の話で、後者をやらないと結局どちらの書き方でも詰む。

渡した ID が全件揃っているかは検証されない

配列に無い ID の行は単に更新されず、テーブルに無い ID は無視される。部分的なリストを渡すと歯抜けになる。

-- 存在しない ID と、既存の ID を1件ずつ渡す
UPDATE tags SET "order" = c.new_order
FROM unnest(ARRAY['9999…', '2222…']::uuid[]) WITH ORDINALITY AS c(tag_id, new_order)
WHERE tags.tag_id = c.tag_id;

UPDATE 1
    name    | order
------------+-------
 Postgres   |     1
 Rails      |     2
 TypeScript |     2     ← 重複した

「そのワークスペースの全タグが過不足なく渡ってきているか」の検証はアプリ側の責任。更新件数が tagIds.length と一致するかを見るのが手軽。

空配列は型注釈が要る

要素型を推論できないので、::uuid[] を付けていないと空配列で落ちる。

SELECT count(*) FROM unnest(ARRAY[]) WITH ORDINALITY AS c(tag_id, new_order);

ERROR:  cannot determine type of empty array
HINT:  Explicitly cast to the desired type, for example ARRAY[]::integer[].

::uuid[] を付けておけば 0 件として素通りする。型キャストは join のためだけでなく、この点でも効いている。

テナントスコープを WHERE に明示する

WHERE tags.tag_id = c.tag_id だけだと、ID さえ合えば他ワークスペースの行も更新できてしまう。

-- workspace_id が一致しなければ更新されない
UPDATE tags SET "order" = c.new_order
FROM unnest(ARRAY['1111…']::uuid[]) WITH ORDINALITY AS c(tag_id, new_order)
WHERE tags.tag_id = c.tag_id
  AND tags.workspace_id = 'bbbb…'::uuid;

UPDATE 0

RLS を入れていれば通常はそちらが守ってくれるが、管理者向けの経路で RLS をバイパスしている場合はその防御が効かない。一般ユーザー経路と管理者経路が同じサービス関数を通るなら、SQL 側で常にスコープを書いておくのが安全。生 SQL に落ちた時点でアプリ側のデフォルトスコープも外れるので、これは Prisma でも Rails でも同じ話になる。

参考

• PostgreSQL: Set Returning Functions（unnest / WITH ORDINALITY）
• PostgreSQL: UPDATE ... FROM
• PostgreSQL: SET CONSTRAINTS（DEFERRABLE）
• Prisma: Raw queries（$executeRaw）
• Rails API: sanitize_sql_array
• 関連: RLS
• 関連: HTML Drag and Drop API の基本（dragstart・dragover・drop・dataTransfer）
• 関連: トランザクションと分離レベル