cakePHP1.3でデータベース操作の研究（MySQL）（２）

今回はまだ調べてないデータベース操作を試してみます。試したいのは、テーブルを検索するときにフィールドの条件にリストを使うこと。リストを使う場合に部分一致が可能かどうかというのも試したい。あと、日付関連のデータをテーブルに格納する最適な方法と、格納されたテーブルの最適な検索等の方法を調べたい。

今回は、前回の英単語帳ではなく極めてシンプルなテーブル構成にしたい。テーブルは１つだけあり、名前はusersである。cakePHPのモデル名はよってUserになる。ちなみにモデルのファイル名は、user.phpである。フィールドは、id、name、have、wantの４フィールドを持つ。idはプライマリキーでauto_incrementにする。単なるテストなので、idは適当にmediumint(9)にしてある。nameはvarchar(50)で、haveとwantはtextにした。

今回のお試し用に作成した、test_controller.phpの全文は下記のとおり。

<?php
class TestController extends AppController{
    public $name = 'test';
    public $uses = array('User');

    public function index(){
        $this->del();
        $this->put_user();
        $result = $this->search();
        $this->set('result',$result);
    }

    private function put_user(){
        $put_cnt = 2;
        for($i=0;$i<$put_cnt;$i++){
            if($i==0){
                $data = array('User' => array(
                    'name' => $i,
                    'have' => '1,2,3,4',
                    'want' => '5,6,7,8'
                ));
            }else{
                $data = array('User' => array(
                    'name' => $i,
                    'have' => '5',
                    'want' => '1'
                ));
            }
            $this->User->create();
            $this->User->save($data);
        }
    }

    private function del(){
        $user = $this->User->find('all',null);
        foreach($user as $u){
            $this->User->delete($u['User']['id']);
        }
    }

    private function search(){
        $user = $this->User->findByName('0');
        $list_h = $this->str_to_list($user['User']['have']);
        $list_w = $this->str_to_list($user['User']['want']);

        $params = array(
            'conditions' => array(
                'User.have' => $list_w,
                'User.want' => $list_h
        ));
        $user = $this->User->find('all',$params);
        return $user;
    }

    private function str_to_list($str){
        if($str == ''){
            return array();
        }else{
            return split(&quot;,&quot;, $str);
        }
    }
}
?>

indexにアクセスする度に全データが削除されて、put_userに指定されたとおりにユーザが作成される。上記だと２ユーザ作成される。haveとwantにそれぞれ指定の文字列を格納している。文字列は数字をカンマ区切りで繋げたものになっている。ユーザを作成したら、一番最初に作成されたユーザ（name==0)のhaveとwantを取り出し、それぞれ、カンマ区切り文字列をリストに変換している。変換したリストを使ってユーザデータを検索している。その検索結果をViewにセットして終了。

上記はうまくいった。findのconditionsに、リストを使うこと自体は全く問題ないのだ。しかし、これは部分一致ではないようだ。リストの各要素と完全に一致する場合のみヒットしている。部分一致といえばLikeというのがあるが、試したところエラーになった。

SQL Error: 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IN ('5', '6', '7', '8') AND `User`.`want` LIKE IN ('1', '2', '3', '4')' at line 1 [CORE/cake/libs/model/datasources/dbo_source.php, line 684]

リストを使うとSQL的にはIN(1,2,3)というような文を使うことになるようだ。どうもこのINにはLikeは使えないらしい。INでなければLikeは使えた。あとは正規表現だなと思って調べたところ一応あるらしい。結構簡単そうだ。Likeよりも強力なRLIKEだ。REGEXPが正式名称っぽいがどっちでもいいらしいので、簡単なRLIKEにしたいと思う。とはいえこれも恐らくINには使えないだろう。一応試してみよう。

やっぱり使えなかった。ということは、部分一致をリストで使う場合は、リストを使わずに正規表現だけでやらないといけないのだ。それならきっとできるだろう。正規表現にはこういう表記法がある。'abc|ab'これはabcかabにマッチするのだ。でもこれは連続して使えるのかな？使えなそうだ。どうしたらいいのだ！？まあ一応試してみよう。

おお！エラーがでない！SQLではこうなってるようだ。どうやらこれでいいようだ。

SELECT `User`.`id`, `User`.`name`, `User`.`have`, `User`.`want` FROM `users` AS `User` WHERE `User`.`have` REGEXP '5|6|7|8' AND `User`.`want` REGEXP '1|2|3|4'

ということは、ユーザのhaveとwantの文字列を最初からカンマ区切りではなくて、"|"区切りにしておけば実に効率的だろう。一応試してみよう。修正版のコントローラー全文は下記になります。

<?php
class TestController extends AppController{
    public $name = 'test';
    public $uses = array('User');

    public function index(){
        $this->del();
        $this->put_user();
        $result = $this->search();
        $this->set('result',$result);
    }

    private function put_user(){
        $put_cnt = 2;
        for($i=0;$i<$put_cnt;$i++){
            if($i==0){
                $data = array('User' => array(
                    'name'    => $i,
                    'have'    => '1|2|3|4',
                    'want'    => '5|6|7|8'
                ));
            }else{
                $data = array('User' => array(
                    'name'    => $i,
                    'have'    => '5|6',
                    'want'    => '7|8|1'
                ));
            }
            $this->User->create();
            $this->User->save($data);
        }
    }

    private function del(){
        $user = $this->User->find('all',null);
        foreach($user as $u){
            $this->User->delete($u['User']['id']);
        }
    }

    private function search(){
        $user = $this->User->findByName('0');

        $params = array(
            'conditions' => array(
                'User.have REGEXP'    => $user['User']['want'],
                'User.want REGEXP'    => $user['User']['have']
        ));
        $user = $this->User->find('all',$params);
        return $user;
    }
}
?>

日付関連のデータ

次は日付関連のデータになります。テーブルを作成した日時とか更新した日時とかを入れておいて新しい順に表示するとか、古い順に削除するとかそういうことをしたい場合に使います。もちろん、作成日時とか更新日時とかを表示したい場合も使います。さて、いつかどこかのサイトで超簡単にやれる方法をちらっと見た気がするのでそれを探してきます。どこだったかなあ。

ありました！3.7.2.3 created と modifiedにありました。このページに下記のように記載されています。

データベースのテーブル内で datetime フィールドとして created または modified フィールドを定義すると、CakePHP はそれらのフィールドを認識し、レコードがデータベースに作成・更新されるときに自動的に埋め込まれます（保存されるデータが既にこれらのフィールドの値を含んでいる場合を除いて）。
created や modified フィールドは、レコードが一番初めに追加されたときに、現在日時をセットします。modified フィールドは、すでに存在するレコードが保存されたときに、現在日時で更新されます。
注意: フィールド名 updated は modified と同じ振る舞いをします。これらのフィールドは、datetime フィールドである必要があり、デフォルト値として CakePHP で認識される NULL をセットします。

もしModel::save()の直前に$this->dataがupdated、created、modifiedなどの値を（Model::readやModel::setを経由して）保持しているなら、これらは自動的に更新されることはなく、$this->dataから値をとってくることになります。

では、上記を参考にやってみたいと思う。まずは、usersテーブルにフィールドを追加しよう。まあusersしかないし、上のコードだとほぼ同時に２つのuserを作成するのだから、実質的な意味はないが、とりあえず自動的にcreatedにセットされるかを試してみて、うまくいったら終了としよう。 usersにはcreatedフィールドを追加します。datetimeフィールドにし、初期値をnullにしておきます。

うまくいった。

Logicky Blog

Logickyの開発ブログです

cakePHP1.3でデータベース操作の研究（MySQL）（２）

日付関連のデータ