PostgreSQL

PostgreSQLで表示順番に連番を登録する

概要

小規模なメッセージ管理ツールで並び替えを行いたいとの要望があったので
連番を一括で登録する方法をメモしておく。

要件

  • 並び替え対象:表示中のメッセージ(10件程度)
  • ドラッグ&ドロップで並び替えたい
  • 新規登録時に、指定がなければ1番目に登録したい
  • 新規登録・更新時に指定した番号に差し込みたい

以上

設計

件数がしれているので、表示順番を変更する場合は、
シンプルに表示中のメッセージすべてに表示順番を振り直す仕様とする。

表示順番の振り直し方法

postgresで表示順番などのカラムに連番を設定する。

対象テーブル(sample)

  • ID (id) ※ 主キー
  • タイトル (title)
  • メッセージ (message)
  • ステータス (status) ※ 1:公開, 2:非公開
  • 表示順番 (sort)
  • 登録日時 (created_at)
  • 更新日時 (updated_at)
  • 論理削除フラグ (is_deleted)

一括更新するSQL

UPDATE
  sample AS t1
SET
  sort = rank
FROM (
  SELECT
    id,
    ROW_NUMBER() OVER (ORDER BY sort IS NOT NULL, sort ASC, updated_at DESC) AS rank
  FROM
    sample
  WHERE 
    status = 1 
    AND is_deleted = false
) AS t2
WHERE 
  t1.id = t2.id
;