はじめに
どうもこんにちは torihaziです
今日は絶賛SQL祭りです。
プログラミング始めたてでやった生のSQLを、
Railsに毒されすぎて、生のSQLなんてほとんど書かなくなった今になって
書かなければならなそうな事態に陥り、
案の定詰まった。
が、無事解決できたのでそれを書き残そうと思います。
本当に何もSQL理解してへんやったんなと思います。
ちなみにそれ動かないでしょ、みたいなコードもそのままエラー付きで書いてます。
最後には解決してると思うので温かい目で見てください。
ふぁいと
どういうもの？
状況を説明すると、4つテーブルがあります。
- A has many B
- B has many C
- C has many D
- C にはversionという概念がある。(履歴テーブル？というのかしら)
このような時、あるA(idが渡されるとする)に紐づくB、Bに紐づくCでかつその中でも最新のCに紐づくDを合わせて取って来たいとする。
この時にどうするかという。
やってみる
とにかく、ひたすらに INNER JOINすればいいのはわかるが最新のCというところだけ気を使う必要がありそう。
第一段階は
SELECT
*
FROM
A
INNER JOIN B on A.id = B.a_id
INNER JOIN C on B.id = C.b_id
INNER JOIN D on C.id = D.c_id
WHERE
A.id = "hogehoge"
ここから最新のCということで頑張っていく。
色々調べてサブクエリというのを使うのが良さそうだということでやってみた。
第二段階
SELECT
*
FROM
A
INNER JOIN B on A.id = B.a_id
INNER JOIN (
SELECT
B.*,
MAX(B.version)
FROM
B
GROUP BY B.key
) as latest_B on latest_B.id = C.b_id
INNER JOIN D on C.id = D.c_id
WHERE
A.id = "hogehoge"
これでいけるのかしら。
column "B.id" must appear in the GROUP BY clause or be used in an aggregate function
なーに、これ。
GROUP BYを使っているのに集約関数を使ってないから、だそうだ
集約関数って何。
らしい。MAXあるやん。なんでや。
GROUP BYを使用した時には、SELECTでは集約関数を伴わない限りは、基本的にGROUP BYで指定した項目しか記述できません。
なるほど。今回はGROUP BY で B.keyの指定しかしていないのにも関わらず、selectでそれ以外のものも出力しようとしたから
エラーが出たのか。
だとしたら？
どうすればいいのかというと、
SELECT
*
FROM
A
INNER JOIN B on A.id = B.a_id
INNER JOIN (
SELECT
MAX(B.version)
FROM
B
GROUP BY B.key
) as latest_B on latest_B.id = C.b_id
INNER JOIN D on C.id = D.c_id
WHERE
A.id = "hogehoge"
サブクエリのSELECTに指定できるものが集約関数使ったものかB.keyしか使えないのだが、
それだとのちのINNER JOINでidが持って来れないので、、、
どうしたものか。
B.keyでグルーピングして、versionが新しいものの全てのレコードをselectで持ってきたい。
| B.key | version | 他のdata |
|---|---|---|
| 1 | 0 | hgoe |
| 1 | 1 | huga |
| 1 | 2 | ge |
| 2 | 0 | geee |
| 2 | 1 | fefe |
| 2 | 2 | fefef |
distinct on 使ってみる。
SELECT
*
FROM
A
INNER JOIN B on A.id = B.a_id
INNER JOIN (
SELECT
DISTINCT ON (B.key) *
FROM
B
ORDER BY
B.version DESC
) as latest_B on latest_B.id = C.b_id
INNER JOIN D on C.id = D.c_id
WHERE
A.id = "hogehoge"
こうしたらversionの降順にした後まとめるらしいので、最新が取って来られる仕組みらしい。
だが。
SELECT DISTINCT ON expressions must match initial ORDER BY expressions
distinct onに設定するものはorder byの最初に与えろということでこうした。
SELECT
*
FROM
A
INNER JOIN B on A.id = B.a_id
INNER JOIN (
SELECT
DISTINCT ON (B.key) *
FROM
B
ORDER BY
B.key,
B.version DESC
) as latest_B on latest_B.id = C.b_id
INNER JOIN D on C.id = D.c_id
WHERE
A.id = "hogehoge"
ようやく出た。
ベンダーロックイン
調べてみるとこの言葉に辿り着いた。
DISTINCT ON って postgresqlにしかないらしい。
つまり他のmysqlで動かそうにもダメらしい。
多分実務だとpostgre以外にならなそうだから大丈夫だと思うけど、
嫌な未来は早めに消しとくべきなのでもう少し粘る。
なんか窓関数とか言われるもんがあるらしい。
ROW_NUMBERとかいうやつ。
SELECT
*
FROM
A
INNER JOIN B on A.id = B.a_id
INNER JOIN (
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY B.key ORDER BY B.version DESC) as num
FROM
B
) as latest_B on latest_B.id = C.b_id AND latest_B.num = 1
INNER JOIN D on C.id = D.c_id
WHERE
A.id = "hogehoge"
これでもいけた。
結論
なっがすぎ。
なんなんこれ。
postgresqlから変わらないことを願う。
最後の確かに便利だけど、カラムを一個追加しないといけないのが面倒。
Railsで返す時はシリアライザで外すのかな？
そんなことするならdistinct onの方が良さげな気はする。
どっちがいいんでしょう。
わかるひと教えてください。