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
さようなら。という日本語
日本にも、別れの挨拶は存在する
お疲れ様、という日本語
お忘れではないだろうか
使ってみた
では、お疲れ様とは
結論
そういうわけで
おつか〜!!!!!
ブログを始める
ブログを始めるのだ。
といっても、長文を書くときに使うため、別のところに持っていた。しかし、長文を書くことがなくなり、全然使わなくなってしまった。ということで、また新たな目的のため開設した。多分こっちはある程度使っていくことになると思う。
書くこと
筆者である月花は、プログラミングによって飯を食っている。そうすると、ことあるごとにTipsが蓄積されていく。別にアウトプットしなくてもいいのだが、テキストファイルでローカルに置いておくのも華がない。そういうわけで、ここで扱っていくこととする。したがって、最新の情報を発信するのではなく、個人的な備忘録として扱っていく。
そのほか、ある1つのことに対して15分以上考えてしまったのなら、それはそこで捨てるのはもったいないように思う。であれば残したいのだが、やはりテキストファイルでローカルに置いても仕方ない。
これらのような、日々のTipsや思考を残して行きたい。要するに、Twitterからアホでないときの私を切り離したかったのである。
最初の記事である、ということ
ホント何書いて良いのかわからない。良いも悪いも無いのだが。デザインの調整やらなんやらのため、何か書かないといけない。ので、とりあえず書いた。
ただ、このような最初の記事というのは、既にTwitterという地面にどっしり構えてしまった私にとってはややナンセンスで、恥ずかしくもある。
いったい何に照れているのかわからないが、これで筆を置くこととする。