重複数をカウントし、その数をインサートするクエリーを1文で行いたい。

Question

起きている問題

faxをシステムから送信した時に、送信した履歴をデータベースにインサートし、送信履歴を閲覧できる画面を作っています。
送信済みのfaxに一部訂正をして、再送信した際は「再送信」という目印をつけようとしています。
送信履歴の表示時に、再送信のカウントをするとページ表示が遅くなると思ったので、インサート時に再送信フラグをつけようと思いました。
faxを大量送信した場合のことを考えて、クエリは最小で済むように考えたいですが、クエリの書き方がわかりません。

行いたいこと

例えば以下のfaxテーブルがある時、fax_codeが「a2」であるレコードを更に2回インサートした時、期待する結果となってほしいです。

faxテーブル

id	fax_code	resend
1	a1	0
2	a2	0
3	a3	0

期待する結果

id	fax_code	resend
1	a1	0
2	a2	0
3	a3	0
4	a2	1
5	a2	2

試したクエリ

INSERT INTO `fax` (`id`, `fax_code`, `resend`) VALUES ('', 'a2', case when select count(`fax_code`) from `fax` where `fax_code` = 'a2' = 0 then '1' else '0' end)

MySQLのバージョンは5.7です。
レコードは削除されることはありません。
1文で行いたい理由は、コードをより簡潔にしたい為です。
よろしくお願いいたします。

Comments

[yambejp]

mysqlのバージョンはなんでしょう？

[m.ts10806]

起きている問題を記載してください。
あと、PHPは直接関係ないのでは？

[sazi]

なぜ1文で行いたいのですか？
また、削除される事は無いのですか？

Answer 1

resendをわざわざカラムで持たずに、SQL側で表示の際に
計算して出す方が現実的かもしれません
スタティックに数値がはいっていると任意の行が削除されたときは
振り直す必要がでてくるため

Comments

[yambejp]

バージョンによってはwindow関数が使えたり
そうでなくてもランク処理などresend部分は容易に得られます
viewを作っておけばアクセスも違和感ないでしょう

[退会済みユーザー]

postgresqlなら、row_number() over(partition by fax_code order by id asc) とか書けば1始まりで求まるので、-1すれば望むresendの値になると思った。

[justicebot]

状況や問題など、詳細に書かないまま投稿してしまいすみませんでした。
スタティックに数値を入れるのは良くないとのこと、勉強になりました。
ご回答いただき、ありがとうございました。

Answer 2

• IDは1はじまりで、行が挿入されるごとに1ずつインクリメントする
• resendは0はじまりで、同じfax_codeの行が挿入されるごとに1ずつインクリメントする

やりたいことは↑であっていますか? であれば以下のようなクエリで一応実現することはできます (fax_codeはa2としています)。

INSERT INTO fax (id, fax_code, resend) (
  SELECT
    COUNT(*) + 1,
    'a2',
    COUNT(fax_code = 'a2' OR NULL)
  FROM fax
);

ただトランザクションやロックの設定によってはidやresendが重複して採番される可能性もあります。IDの採番にはシーケンスやAUTO_INCREMENTを利用する、resendはそもそも値として持たずにSELECTのたびに都度計算するなどしたほうがよいと思います (そもそもテーブル設計を見直すことができない環境・状況であればご放念ください)

Comments

[justicebot]

やりたいことが無事に行えました。ありがとうございました。