読者です 読者をやめる 読者になる 読者になる

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を熟知していようが、自分でこういうの書くのは絶対ダメ。顧客が思い通りにデータを入れると思うな。

もしこれがJSONXMLであれば、大概の言語は基本実装なりライブラリがあるので、このクエリを発行したモジュールの言語で対応することができる。

今回は、JSONにして、PHPでパースする。

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関係の関数が一覧になっている。
PostgreSQLjsonをこねくりまわして変態になろう!
JSON関数と演算子 : https://www.PostgreSQL.jp/document/9.4/html/functions-json.html

さようなら。という日本語

日本にも、別れの挨拶は存在する

「さようなら」だ。これだけでお気付きの方もいるとは思うが、続けさせてもらうと、「使ってないよね?」ということである。
 

お疲れ様、という日本語

現代の日本人はとにかくお疲れだ。会ったらまず「お疲れ様です」別れるなら最後の言葉は「お疲れ様でした」である。なんなら乾杯の音頭も「おーつかれさまでーす↑!!!」でもある。なお、筆者は諸々の事情により、「おつか〜!」を多用している。
いったいいつから我々は「さようなら」を使わなくなったのだろうか。

お忘れではないだろうか

1番最初に習うはずの別れの挨拶は、「さようなら」だ。とはいえ、口語的でないという感覚は幼稚園児でも有り、先生には「さようなら」、友達には「バイバイ」「じゃあね」である。
ただ、成人してから聞いてみた所によると、「さようなら」はやはり、「せんせーさよーなら」のように、先生という単語とセットになるようだ。納得である。

使ってみた

ここ数ヶ月ほど、「さようなら」を使ってみた。今日、苦情がでた。
つまり、「毎日会うならともかく、たまにしか会わないのに」ということである。
確かに私もこれには納得した。小学生のとき、散々「さようなら」と言ってきた相手とは、毎日会うのが前提だったのだ。そもそもたまにしか会わない相手に「またね」とまたチャンスがあることを強調するのは自然であるし、また、受け入れやすい。次のチャンスについて言及できない「さようなら」では今生の別れという感がするのである。なるほどね。

では、お疲れ様とは

それなら、「お疲れ様」はどうか。これはそもそも、別れについて言及していない。本来は相手をねぎらう言葉だ。足労頂いたねぎらいとしてのそれもあるだろうし、一緒に何かしてくれた事に対する、疲れさせてしまったね、という意味でのそれもあるだろう。
「お疲れ様」自体は相手を思いやるただそれだけで、別れについては何も言及せず、ただただ労う単語である。何をしてもされても、まず労いから、という非常に日本人らしい言葉だ。それを言ったということでも、言われたということでも、お互いに日本人らしい部分を満たすことができる。であれば、これを使ったほうが無難なのだ。

結論

「さようなら」は最初に習う別れの挨拶ではあるが、成人後の交友関係で使うにはいささかシンプルすぎ、別れの挨拶であることしか伝えることができない。であれば、「またね」「じゃあな」の方が良いに決まっていたのだ。つまり、交友関係の変化による必然の淘汰であったのだ。

そういうわけで

おつか〜!!!!!

ブログを始める

ブログを始めるのだ。

といっても、長文を書くときに使うため、別のところに持っていた。しかし、長文を書くことがなくなり、全然使わなくなってしまった。ということで、また新たな目的のため開設した。多分こっちはある程度使っていくことになると思う。

書くこと

筆者である月花は、プログラミングによって飯を食っている。そうすると、ことあるごとにTipsが蓄積されていく。別にアウトプットしなくてもいいのだが、テキストファイルでローカルに置いておくのも華がない。そういうわけで、ここで扱っていくこととする。したがって、最新の情報を発信するのではなく、個人的な備忘録として扱っていく。

そのほか、ある1つのことに対して15分以上考えてしまったのなら、それはそこで捨てるのはもったいないように思う。であれば残したいのだが、やはりテキストファイルでローカルに置いても仕方ない。

これらのような、日々のTipsや思考を残して行きたい。要するに、Twitterからアホでないときの私を切り離したかったのである。

最初の記事である、ということ

ホント何書いて良いのかわからない。良いも悪いも無いのだが。デザインの調整やらなんやらのため、何か書かないといけない。ので、とりあえず書いた。

ただ、このような最初の記事というのは、既にTwitterという地面にどっしり構えてしまった私にとってはややナンセンスで、恥ずかしくもある。

いったい何に照れているのかわからないが、これで筆を置くこととする。