PHP : エクスクラメーションマーク2つによる二重否定
月花です。
ちょっとコードリーディングをしていて、なんだこれって思ったのでまとめてみます。
<?php $hoge = !!$foo;
というように、PHPでexclamation markによる二重否定( double not ) を行うコードが見つかった。
最初は double not だと思っていたのだが、実はどちらかというと double not ではなく twice not で、「二重否定」というよりは「否定の二重適用」というような挙動だった。
毎度のことながらここまでは検索用の文なので、以降は日本人に戻ってカタカナで書きます。
エクスクラメーションマーク2つによる二重否定
<?php $hoge = !!$foo;
というようなコードを見つけた。
2文字の演算子なので、一見比較演算子か論理演算子かと思ったのだが、コードの文脈上違うようだ。
また、代入を行っているので、なんらかのキャストのようだと思ったのだが、結論から言うと
<?php $hoge = (bool)$foo;
と同義のようだ。
なぜ二重否定でboolean型へのキャストとなるのか
二重否定は、このように置き換えられる。
<?php $hoge = !( !$foo );
否定を行う論理演算子を一度適用し、さらにもう一度否定している流れである。
PHPではエクスクラメーションマークによる否定ではboolean型への暗黙的なキャストが行われ、boolean型にキャストされたものに対してもう一度否定をしてやることで、キャストしつつ元々の結果に反転させることで、遠回りにキャストを実現している。
冗長に書き直すと、
<?php $hoge = !$foo; // 暗黙的なキャストと論理否定 $hoge = !$hoge; // 2回目の論理否定
となる。
これは、
<?php $hoge =1; var_dump($hoge); // int(1) var_dump($hoge . ''); // string(1) "1"
というような変換と似た、演算での暗黙変換を利用するキャストの手法である。
PHPでの比較演算
では、2つの変数の比較結果のbooleanを変数に押し込めたい時、どうすればよいだろうか。
PHPでは比較演算の結果は必ず返却されるため、if文の条件式中で使うだけではなく変数に代入することもできる。
つまり、
<?php if( $hoge == $piyo ){ $result1 = true; }else{ $result1 = false; } $result2 = ($hoge == $piyo) ? true : false; $result3 = ($hoge == $piyo);
これらはすべて等価である。
他のboolean型への変換手法
二重否定によるキャストは、単純に2つ書いただけでそんなことが可能であると知らなければ読めないので、いささか可読性に欠けると言える。
では、他にスマートな書き方はないだろうか。
以下は全て二重否定と等価の書き方である。
<?php // 二重否定 $hoge = !!$foo; // 明示的キャスト $hoge = (bool)$foo; // 変換関数 $hoge = boolval( $foo ); // ifの利用 if( $foo ){ $hoge = true; }else{ $hoge = false; } $hoge =$foo ? true : false; // trueとの比較 (falseとの比較もできる) if( $foo == true ){ $hoge = true; }else{ $hoge = false; } $hoge = ($foo == true) ? true : false; $hoge = ($foo == true); // trueとの論理積 $hoge = ($foo and true); $hoge = ($foo && true);
もっとありそうだけど、思いつくのはこれくらい。
結局読みやすくてスマートなのは、boolval() 、 (bool)、trueとの比較、 三項演算子かな、と思う。
論理積はスマートだけど、他に良い方法あるし、これをあえて選ばなくても、というくらいかな。
リテラルへの適用
ちなみに、$fooの部分はリテラルであってもよい。
<?php $hoge = !!'0'; $hoge = !!0; $hoge = !!(1+2+3);
ここで、ちょっとガチャガチャとやってみよう。
PHPでは、0以外の数字、及び、'0'以外または''以外の文字列リテラルはtrueとなる。
これらを . 演算子で繋ぐことによって、整数リテラルを文字列リテラルに変換することができる。
では、これと二重否定を絡めてみよう。
<?php $foo = 1; // 整数リテラル $hoge = !!( $foo.'' ); // 整数リテラルを文字列リテラルにして、それを二重否定してboolean var_dump( $hoge ); // bool(true)
この通りたった3行で型がゴシャゴシャになるので、絶対にやるなよ！！！！！！
そんなことしねえと思ったそこのお前！！！！！
長いこと煮詰まったコードではこれらが分散して、たとえば$fooの初期化が120行目、リテラル変換が140行目、二重否定が170行目にそれぞれあったりするんだからな！！！！その間に煩雑なコードが追加されていったりするから$fooについて追うまで気づかないんだ！！！！！
絶対にこの手の成長をする種を蒔くな！！！！！！！
結論:どれ使ったら良いの
これはチームが採用している規約などに応じて変えるべきだとは思うが、規定がないのであれば、ボトムの実力に合わせるべきである。
教科書的なif文しか理解されそうもない、という環境ではやはり
<?php if( $foo == true ){ $hoge = true; }else{ $hoge = false; }
が強いと思う。
これは教科書みたいな技術書にも最初の方に書いてあるようなif文でしかないので。
それ以上の実力があるなら、キャストかboolval()の利用。
演算結果を変数に押し込めることを知っているレベルなら、三項演算子や論理積が強いと思う。
見た目にもtrueとの演算であることがすぐわかり、そもそもtrueとfalseの文字列が出てくるのでgrepや目検にも強いという有用性がある。
もしくは、何らかの定数が導入されて比較対象を変える場合にも、明示的に比較演算子やtrueなどbooleanリテラルがコードに現れているとわかりやすい。
二重否定は、それがシンタックスエラーではなく成立するということを知らない限り使えない局所的な知識なので、推奨はできない。
PostgreSQL : 正規化されたテーブルをSQLだけで入れ子のJSONにする
月花です。
SQL芸人としての活動のメモです。
今回は、正規化された一対多のテーブルから、多の方を一行を連想配列のJSONにしながら、複数の行をJSONにする、入れ子構造のJSONを抽出します。
結果的になんてことない単純なクエリになったのですが、割とパズル的に悩んでしまったので、残しておきます。
3人の生徒に、それぞれに2回の試験があり、その合計6つの結果を生徒ごとに分割して描画したい、というシナリオで書いていきます。
例では、PHPでHTMLを出力することにします。
通常であれば、普通にJOINして6レコードをループし、
生徒ごとにまとめてさらに3回のループを行うか、
ソートしてから、前のループと生徒IDが違えば閉じタグと開きタグを出力する、
みたいなことになりますが、条件式多くて読みづらいです。
なので、生徒ごとにまとめるところまでSQLにやらせてしまいましょう。レコードの集約はJSONにすれば、言語に依存しません。
目次です。
- テーブル構造
- 生徒テーブル students
- 試験テーブル exams
- 生徒-試験リレーションテーブル student_exams
- この例での求めたいデータ
- 普通にJOINしてみた場合
- 理想の抽出結果
- アプローチ
- 取れたレコードをPHPで扱う
テーブル構造のサンプル
例では、生徒テーブルと試験テーブル、そしてそれらを繋ぐ生徒-試験リレーションテーブルの3つのテーブルが存在しているとする。
そこから、生徒テーブルを軸にして、生徒数分のレコードの中に、複数の試験をまとめて収めるJSONを書いていく。
生徒テーブル students
| student_id | name |
|---|---|
| 1 | 田中 |
| 2 | 佐藤 |
| 3 | 木村 |
試験テーブル exams
| exam_id | name | date | math_point | japanese_point | english_point |
|---|---|---|---|---|---|
| 1 | 中間試験 | 2017-09-10 | 10 | 20 | 30 |
| 2 | 中間試験 | 2017-09-10 | 40 | 50 | 60 |
| 3 | 中間試験 | 2017-09-10 | 70 | 80 | 90 |
| 4 | 期末試験 | 2017-09-30 | 15 | 25 | 35 |
| 5 | 期末試験 | 2017-09-30 | 45 | 55 | 65 |
| 6 | 期末試験 | 2017-09-30 | 75 | 85 | 95 |
なお、本来であれば試験名と日付も正規化するべきであるが、今回の話にはそれほど影響しないので、簡略化のために今回は正規化しない。
生徒-試験リレーションテーブル student_exams
| student_id | exam_id |
|---|---|
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 1 | 4 |
| 2 | 5 |
| 3 | 6 |
この例での求めたいデータ
普通にJOINしてみた場合
SELECT s.*, e.* FROM students s JOIN student_exams se ON s.student_id=se.student_id JOIN exams e ON se.exam_id = e.exam_id;
で、こうなる
| student_id | name | exam_id | name | date | math_point | japanese_point | english_point |
|---|---|---|---|---|---|---|---|
| 1 | 田中 | 1 | 中間試験 | 2017-09-10 | 10 | 20 | 30 |
| 2 | 佐藤 | 2 | 中間試験 | 2017-09-10 | 40 | 50 | 60 |
| 3 | 木村 | 3 | 中間試験 | 2017-09-10 | 70 | 80 | 90 |
| 1 | 田中 | 4 | 期末試験 | 2017-09-30 | 15 | 25 | 35 |
| 2 | 佐藤 | 5 | 期末試験 | 2017-09-30 | 45 | 55 | 65 |
| 3 | 木村 | 6 | 期末試験 | 2017-09-30 | 75 | 85 | 95 |
しかし、これでは、たとえばPHPでこれを処理する場合、愚直にやれば6回ループになり、テーブルならtrタグが6つできたりする。
そういうテーブルを描画するならそれでいいが、生徒ごとのブロックとして閉じタグを挟みたい場合に厄介となる。
なので、3つにどうにか収めるように配列を操作するループを別途書いたり、ソートしてから生徒が変わったかを検知するif文を書いたりしないといけないので、端的にいってクソである。
なので、3レコードにしたい。
理想の抽出結果
| student_id | name | exam_json |
|---|---|---|
| 1 | 田中 | {試験内容のJSON} |
| 2 | 佐藤 | {試験内容のJSON} |
| 3 | 木村 | {試験内容のJSON} |
こうなればよい。
このJSONには、例でいえば、配列長が2で、その中には試験のデータをいれた1つの連想配列がはいっていればいい。
3回のループで生徒ごとに区切りつつ、内部のループで2つの試験のデータを出力すればいいのだ。
この条件に合う結果のJSONは、たとえばこうなる。
"[ {"student_id":1,"student_name":"田中", "exam_data":[ {"exam_id":1,"exam_name":"中間試験","date":"2017-09-10","math_point":10,"japanese_point":20,"english_point":30}, {"exam_id":4,"exam_name":"中間試験","date":"2017-09-30","math_point":15,"japanese_point":25,"english_point":35} ] }, {"student_id":2,"student_name":"佐藤", "exam_data":[ {"exam_id":2,"exam_name":"中間試験","date":"2017-09-10","math_point":40,"japanese_point":50,"english_point":60}, {"exam_id":5,"exam_name":"中間試験","date":"2017-09-30","math_point":45,"japanese_point":55,"english_point":65} ] } ]"
長くなったが、このJSONを作り、上のレコードを抽出することを目的とする。
アプローチ
まず、これを実現するためには、2回の集約が必要となる。
横に広がっている試験データを連想配列に集約する
まずは、試験データを1レコードずつ連想配列に変換していく。
次のステップで生徒IDを使うので、ここで生徒-試験リレーションテーブルをJOINして、生徒IDも含めてしまおう。
| student_id | exam_id | json |
|---|---|---|
| 1 | 1 | {試験内容のJSON} |
| 1 | 4 | {試験内容のJSON} |
| 2 | 2 | {試験内容のJSON} |
（省略）
このためには、json_agg関数を用いる。
生徒を軸に、試験テーブルの1レコードごとに集約するために、GROUP BYには生徒ID、試験IDを指定して、試験テーブルのデータを集約する。
そのためのクエリは、単純に上記を落とし込んで、こうなる。
SELECT student_id, e.exam_id, json_agg(e) AS json FROM student_exams se JOIN exams e ON se.exam_id=e.exam_id GROUP BY student_id,e.exam_id ORDER BY student_id ASC
すると結果は、このようになる。
| student_id | exam_id | json |
|---|---|---|
| 1 | 1 | {[{"exam_id":1,"name":"中間試験","date":"2017-09-10T00:00:00","math_point":10,"japanese_point":20,"english_point":30}] |
| 2 | 4 | [{"exam_id":4,"name":"期末試験","date":"2017-09-30T00:00:00","math_point":15,"japanese_point":25,"english_point":35}] |
| 3 | 2 | [{"exam_id":2,"name":"中間試験","date":"2017-09-10T00:00:00","math_point":40,"japanese_point":50,"english_point":60}] |
（省略）
ちなみに、json_agg関数の引数をカラムではなくテーブルにした場合、 table_name.* と同義になる。
すべてのカラムを出すしかないので、カラムを絞りたい場合は、サブクエリにするかWITH句で追い出しておく必要がある。
ここで、当初の目論見とはことなり、連想配列が配列長1の配列に入ってしまっていることに留意しておく。
生徒ごとに連想配列にした試験データを集約する
次に、生徒ごとに集約するので、今度はGROUP BYに生徒IDのみを指定し、再度json_aggにかけてやればよい。
postgresのjson_aggは内部でjson型を扱えるため、
JSONの中に「JSONの形をしたstring」が入ってしまい、最終的にjson_decodeした結果をjson_decodeする必要がある、
なんてことにならないようなツリー状にまとめてくれる。
そのため、そこのところは考慮せず、単純にさきほどのクエリをサブクエリとして、json_aggをかけてやるだけでよい。
SELECT student_id, json_agg(jsons.json) AS exam_json FROM ( --ここから SELECT student_id, e.exam_id, json_agg(e) AS json FROM student_exams se JOIN exams e ON se.exam_id=e.exam_id GROUP BY student_id,e.exam_id ORDER BY student_id ASC --ここまで先程のクエリ ) jsons GROUP BY student_id;
これだけでよい。
そして結果は下記となる。
| student_id | exam_json |
|---|---|
| 1 | {[[{"exam_id":1,"name":"中間試験","date":"2017-09-10T00:00:00","math_point":10,"japanese_point":20,"english_point":30}], [{"exam_id":4,"exam_name":"期末試験","date":"2017-09-30T00:00:00","math_point":15,"japanese_point":25,"english_point":35}]] |
| 2 | [[{"exam_id":2,"name":"中間試験","date":"2017-09-10T00:00:00","math_point":40,"japanese_point":50,"english_point":60}], [{"exam_id":5,"exam_name":"期末試験","date":"2017-09-30T00:00:00","math_point":45,"japanese_point":55,"english_point":65}]] |
| 3 | [[{"exam_id":3,"name":"中間試験","date":"2017-09-10T00:00:00","math_point":70,"japanese_point":80,"english_point":90}], [{"exam_id":6,"exam_name":"期末試験","date":"2017-09-30T00:00:00","math_point":75,"japanese_point":85,"english_point":95}]] |
生徒3人と試験6回のデータが3レコードとなって収まった。
SQLはここまでで、ここからはビジネスロジックのお話になる。
ここでは、PHPで行うとするが、JSONなので言語には依存しない。
取れたレコードをPHPで扱う
想定仕様は、生徒ごとに開きタグと閉じタグを出力し、生徒ごとのブロックを描画するものである。
取れたレコードを $records に入れたとすると、下記のようなPHPで扱う事ができる。
<?php (省略) // 生徒ごとのループ foreach( $records as $record){ // 開きタグ描画処理 // jsonから配列に変換（オブジェクトに変換するなら第二引数は省略かfalse） $exam_array = json_decode($record['exam_json'], true); // 試験ごとのループ foreach( $exam_array as $exam_data_array ){ // ここで、この中身は配列長が1のデータだったことを思い出そう // 仕様上そうなってしまうだけなので、先頭要素を取るだけで良い $exam_data = current( $exam_data_array ); // $exam_data['name'] や、 // $exam_data['date'] で、試験のデータが取れるので処理や描画 } //閉じタグ描画処理 }
以上で、postgresに仕事をさせ、PHPに楽をさせることができた。
例では単純なものだったのでJOINが少なかったが、本来はもっと正規化してあるはずなのでサブクエリがごちゃつきがち。
WITH句やviewに、いかにわかりやすく追い出すかが出来るSQL芸人になれるかどうかのカギである。たぶん。
Postgres・MDB2のキャスト演算子とプレースホルダ
月花です。
PDOによるデータベース操作の話をします。
今回はこんな話です
ことの発端
MDB2を使ったフレームワークで、Postgresから疑問符プレースホルダを使ったINSERTを行った際に、不思議なエラーが発生した。
INSERT INTO test (id,name,value) VALUES (1,NULL::TEXT,?);
こういうクエリをprepareして、
array('test')を食わせてexecuteするような、一見問題のないINSERT文を実行した。
すると、
〜〜〜 MDB2 Error: unknown error 〜〜〜 [Native message: ERROR: 入力の最後で 構文エラー LINE 1: INSERT INTO test (id,name,value) VALUES (1,NULL$1
とのこと。
不思議なエラー文だ。
糸口を探る
[Native message: ERROR: 入力の最後で 構文エラー LINE 1: INSERT INTO test (id,name,value) VALUES (1,NULL$1
ということで、$1がどうにも気になる。
これはたぶん、プレースホルダだ。
原文では、NULLがベタ書きされている箇所は一個しか無いので、そうすると
NULL::TEXT
が気になってきた。
もしかして
INSERT INTO test (id,name,value) VALUES (1,CAST( NULL AS TEXT ),?);
コロンでのキャスト演算子ではなく、こうなら・・・・
いけた。
ということは、
- コロン2つによるキャストが影響している
- どうも不当にプレースホルダとして解釈されている
- コロンを使わなければ治る
となれば原因は
結局は、
INSERT INTO test (id,name,value) VALUES (1,NULL::TEXT,?);
この、 NULL::TEXT の、 :TEXT が名前付きプレースホルダとして解釈され、 NULL$1 となり、さらに元々の疑問符プレースホルダとの競合もあり、構文エラーとなったようである。
ところが、これそんなはずはなく、正しい文法のはずだ。
ためしにいくつかのSQLクライアントから、疑問符プレースホルダを適当な値に変換したものを実行したが、どれも成功した。
ということは、フレームワーク・・・と思ったがこいつはMDB2との橋渡しをしているだけだ。
というわけでMDB2を探っていく。
環境の再現
適当な VirtualBox でMDB2をインストールして、下記のような環境にした。
# pear list Installed packages, channel pear.php.net: ========================================= Package Version State Archive_Tar 1.4.2 stable Console_Getopt 1.3.1 stable MDB2 2.4.1 stable MDB2_Driver_pgsql 1.4.1 stable PEAR 1.9.5 stable Structures_Graph 1.0.4 stable XML_RPC 1.5.4 stable XML_Util 1.2.3 stable
これで、さくっと書いて実行する。
require_once 'MDB2.php';
$dsn = 'pgsql://***:***@***/***';
$options = array(
'debug' => 2,
'result_buffering' => false,
);
$mdb2 = MDB2::connect($dsn);
if (PEAR::isError($mdb2)) {
die($mdb2->getMessage());
}else{
$sql="INSERT INTO test (id,name,value) VALUES (1,NULL::TEXT,?);";
$statement=$mdb2->prepare($sql);
if (PEAR::isError($statement)){
echo $statement->getDebugInfo();
}else{
$statement->execute(array('test'));
}
}
$mdb2->disconnect();
とすると、やはり同じエラーが出た。
ということで、MDB2が悪いことが確定して一安心。
解決
そもそも、キャスト演算子がコロンでできている以上、検索のしにくさったら。
過去の例が全然見当たらない。
こういうときはとりあえずバージョンアップをしてみよう。
# pear install MDB2-2.5.0b5 # pear install MDB2_Driver_pgsql-1.5.0b4 # # pear list Installed packages, channel pear.php.net: ========================================= Package Version State Archive_Tar 1.4.2 stable Console_Getopt 1.3.1 stable MDB2 2.5.0b5 beta MDB2_Driver_pgsql 1.5.0b4 beta PEAR 1.9.5 stable Structures_Graph 1.0.4 stable XML_RPC 1.5.4 stable XML_Util 1.2.3 stable
とすると、あっさり成功したのでした。
なので、MDB2のChangeLogを読み漁る。
すると、MDB2_Driver_pgsql 1.5.0a1 において、
Changelog: 〜〜〜 - fixed bug #11652: failed prepared queries containing the "::type" style of casting 〜〜〜
なんと、2007-07-20 12:38 UTC に報告された不具合である。
10年前て・・・
CakePHP : 同カラムに対する複数のLIKEを連結する
前回の続き。
前回はPostrgreSQLから配列を取り出す方だったが、次はそれを使う方。
対象のテーブル
データ
| product_id | code | tag | publisher |
|---|---|---|---|
| 101 | 123456 | 書籍,技術書,Java | オライリー |
| 102 | 234567 | 書籍,技術書,PHP | ソフトバンククリエイティブ |
| 103 | 345678 | 書籍,技術書,PHP | ソフトバンクパブリッシング |
| 104 | 456789 | CD,アニメ,アイカツ | ランティス |
NGフィルタ
| target | filter_string |
|---|---|
| tag | CD |
| tag | Java |
| publisher | ソフトバンククリエイティブ |
データに対して、NGフィルタをかけたい、というもの。
結果としては、
| product_id | code | tag | publisher |
|---|---|---|---|
| 103 | 345678 | 書籍,技術書,PHP | ソフトバンクパブリッシング |
こうなることを想定する。
ストアドを使って一撃でやってもいいのだが、メンテが大変なので間にPHPを挟んで、NGフィルタの取得と、その結果をWHERE句に含めた取得クエリの2回発行することにする。
CakePHP のWHERE句指定方法
CakePHPでは、SQLそのものの作成はモデルクラスに任せることになっている。
このため、SELECT句やFROM句、WHERE句の配列をモデルクラスに渡すことまでがコントローラというロールの限界であり、これ以上のところに踏み込んではいけない。
だが、このWHERE句がややこしくてちょっとハマった。
今回のNGフィルタでは NOT LIKE 〜〜〜 AND NOT LIKE 〜〜〜 AND ・・・という風にANDで接続していけばいい。
この配列を作る。
結論から言うと、今回の場合求められる配列は以下のようになる。
<?php $conditions = array( array('tag NOT LIKE'=>'%CD%'), array('tag NOT LIKE'=>'%Java%'), array('publisher NOT LIKE'=>'%ソフトバンククリエイティブ%'), );
明示的に書かない限り、ANDで連結されるようになっている。
また、演算の種類を省略すると、イコールになる。
今回はANDは省略し、NOT LIKE を指定している。
下記のようなシンプルな形もあるのだが、
<?php $conditions = array( 'tag NOT LIKE'=>'%CD%', 'tag NOT LIKE'=>'%Java%', // ←キーの重複 'publisher NOT LIKE'=>'%ソフトバンククリエイティブ%', );
今回は同じカラムに対して同じ判定方法で複数の条件があるため、キーが重複してしまい、後者を使うことができない。
なので、前者を作るために、前回の記事の内容を使う。
WHERE句のもととなる配列を作る
さきほどのこのクエリを使って、
SELECT target, json_agg(filter_string) AS json_filter FROM filters GROUP BY target
こうする
| target | json_filter |
|---|---|
| tag | ["CD","Java"] |
| publisher | ["ソフトバンククリエイティブ"] |
そうなったら、こうする
<?php $conditions=array(); foreach($filters as $filter){ $target=$filter['Filter']['target']; foreach(json_decode($filter['Filter']['json_filter']) as $filterString){ $conditions[]=array($target.' NOT LIKE'=>'%'.$filterString.'%'); } }
すると$conditionsはこうなって完成
<?php $conditions = array( array('tag NOT LIKE'=>'%CD%'), array('tag NOT LIKE'=>'%Java%'), array('publisher NOT LIKE'=>'%ソフトバンククリエイティブ%'), );
実際に取得する
こうだ
<?php $result = $this->Data->find('all', array( 'fields' => array('Data.*'), 'conditions' => $conditions, ));
以上だ！
余談
PHPでは、array()を用いた配列の宣言時にデータを入れる場合、最終要素の後にカンマを余分にいれてもエラーにならない。
一見ややこしい仕様だが、これには実用上かなり助けられている。
たとえば、以前こうなっていたデータが、
<?php $conditions = array( array('tag NOT LIKE'=>'%CD%'), array('tag NOT LIKE'=>'%Java%'), array('publisher NOT LIKE'=>'%ソフトバンククリエイティブ%') //←カンマがないことに注目 );
誰かの編集でこうなったとする
<?php $conditions = array( array('tag NOT LIKE'=>'%CD%'), array('tag NOT LIKE'=>'%Java%'), array('publisher NOT LIKE'= '%ソフトバンククリエイティブ%'), //←カンマがあることに注目 array('publisher NOT LIKE'=>'%ランティス%') );
これらの2ファイルでdiffを取ると、意味上では1行の変更で1要素の追加であるにもかかわらず、カンマが追加されていることによって、感覚的に変更されていない3番目の要素も変更した行となってしまう。
バージョン管理システムやdiffツールで変更があった箇所を精査する際、この元から書いておけばよかったカンマ1個のために、精査する行が増えてしまう。
さらに、よくある変更ではあるので、なんだカンマだけの変更か、と軽く見てしまうと、実はその行にもう1箇所変更があり、文法エラーが新たに混入したことに気づけない。
上記のため、筆者は必ず最後の要素でもカンマを書き込んでいる。
PostgreSQL : 配列を配列として取り出したい
はじめての技術メモです。
今まで雑魚実装をしていたことに気づいたので、覚え書き。
本来はタイトル通り、技術関係のことを書こうかなと思ってたけど、書くほどのことをしなかったので、ようやく記事が書けた。
PostgreSQLから配列を取り出す
例えばこういうテーブルを対象とする。
| product_id | tag |
|---|---|
| 101 | 書籍 |
| 101 | エッセイ |
| 102 | DVD |
| 102 | アニメ |
| 102 | アイカツ |
こういったテーブルから、下記のような形式のデータを抽出したい。
| product_id | array_tag |
|---|---|
| 101 | {書籍,エッセイ} |
| 102 | {DVD,アニメ,アイカツ} |
よくある（？）、正規化されたテーブルをいい感じにするやつ（表現しにくい）。
これをサブクエリにして、商品データとJOINするとかだろう。
これ自体は、下記のようなクエリでサックリいく。
SELECT product_id, array_agg(tag) AS array_tag FROM tags GROUP BY product_id
array_agg() はバージョン8.4以降から使える。
でもこれだとまだちょっと足りない。
正確で堅い手法でパースしたい
上記のクエリを発行した結果では、PostgreSQL内では配列だったとしても、受け取る言語の実装によっては文字列になる。
文字列が返った場合、パースして配列にするのがめんどくさい。
メタ的な意味ではなくデータ自体に波括弧があったら？カンマがあったら？
単純に波括弧を除外してカンマでsplitするだけでは全然ダメ。
たとえRFCを熟知していようが、自分でこういうの書くのは絶対ダメ。顧客が思い通りにデータを入れると思うな。
もしこれがJSONやXMLであれば、大概の言語は基本実装なりライブラリがあるので、このクエリを発行したモジュールの言語で対応することができる。
array_agg の結果をJSONにする
PostgreSQL バージョン9.3 からは、json_aggという集約関数がある。
array_aggのjson版となっている。
PostgreSQL内では9.2からjson型が存在するので、json型だが、PHPに返ってくるのは当然文字列。
まあjsonだしね。
これをjson_decodeでデコードしてやれば、解決する。
クエリ
SELECT product_id, json_agg(tag) AS json_tag FROM tags GROUP BY product_id
PHP実装例
<?php // $result に上記クエリの結果が入っていると考えて foreach($result as $record){ $productId = $record['product_id']; $decodedRecord = json_decode($record['json_tag']); foreach($decodedRecord as $tag){ //なんか処理 } }
バージョン9.2での実装
上記はバージョン9.3 でのカンタンな実装だが、json実装当初の9.2でも、同様のことは実現できる。
筆者はjson_aggを知らなかったのでこっちの方法でやっていた。
PostgreSQL内で配列からjsonに変換することができるから、array_aggの結果をjsonに変換すればいいだけ。
SELECT product_id, array_to_json(array_agg(tag)) AS json_tag FROM tags GROUP BY product_id
経験上こういう整形は出来る限りのところまでDBにやらせたほうが速いからDBに生きがいを与える。
応用する
下記のページにバージョン9.4系でのjson関係の関数が一覧になっている。
PostgreSQLでjsonをこねくりまわして変態になろう！
JSON関数と演算子 : https://www.PostgreSQL.jp/document/9.4/html/functions-json.html
