あかつきのお宿

MySQLには、DATE_FORMATというのがあります。

何だろ……phpのdate()関数に近いイメージがあるんですが、日付けのフォーマットを指定してデータを取得したり、取得の条件に使ったりすることができます。

CakePHP3になってから、DATE型やDATETIME型の扱いが変わったようで、そのせいなのか、DATE_FORMATの使い方も、CakePHP2の頃と同じようにはいかないことがあります。

今日はその辺を洗ってみたいと思います。

ちなみに、本文はすごーく長いですが、今日の記事を通して僕が言いたいことは、実質この一行だけです。

$query->where(["DATE_FORMAT(created, '%Y-%m') = '2015-11'"]);

なのでここから先は、活字中毒の人が「はあはあ……やばい、活字が切れやがった……頼む……活字を、活字をくれぇ……」ってなった場合に読んでください。

日付けデータの扱い方

まずは、MySQLでの日付けデータの扱い方を簡単におさらいしときましょう。

例えば、postsテーブルにこんなデータが入っているとします。

+----+--------+------+---------------------+
| id | title  | text |      created        |
+----+--------+----------------------------+
|  1 | title1 | text | 2015-09-01 12:00:00 |
|  2 | title2 | text | 2015-09-02 12:00:00 |
|  3 | title3 | text | 2015-09-03 12:00:00 |
|  4 | title4 | text | 2015-10-01 12:00:00 |
|  5 | title5 | text | 2015-10-02 12:00:00 |
|  6 | title6 | text | 2015-10-03 12:00:00 |
|  7 | title7 | text | 2015-11-01 12:00:00 |
|  8 | title8 | text | 2015-11-02 12:00:00 |
|  9 | title9 | text | 2015-11-03 12:00:00 |
+----+--------+------+---------------------+

id・・・ユニークのID
title・・・記事タイトル
text・・・記事本文
created・・・投稿日時

ここから、2015年の11月に投稿されたデータだけを取得したい場合に、どんなSELECT文を投げたら良いか。

例えば、以下のような方法があげられます。

SELECT * FROM posts WHERE created >= '2015-11-01 00:00:00' AND created <= '2015-11-30 23:59:59'
SELECT * FROM posts WHERE YEAR(created) = '2015' AND MONTH(created) = '11'
SELECT * FROM posts WHERE DATE_FORMAT(created, '%Y-%m') = '2015-11'

BETWEENを使うなどの方法も考えられますが、とりあえずこの3つはいずれも、上の表でいうidが7〜9のデータが取得できます。

一番目は特に言うことはないですね。範囲を指定して、データを取って来ています。

二番目のYEAR()とMONTH()は、それぞれ年と月を返す関数です。「YEAR(created) = ‘2015’」で2015年のデータを、さらに「MONTH(created) = ’11’」で11月のデータに範囲を絞っているわけですね。

そんで三番目がDATE_FORMAT()です。今回は「%Y-%m」で年月を指定しましたが、年月日を指定したり、年や月だけ指定したり、いろんな使い方ができます。「DATE_FORMAT(created, ‘%Y-%m-%d’) = ‘2015-11-01’」とか書けば、2015年11月1日のデータを取って来れます。

速度の差はあると思いますが、どれを使うかは好みにもよるでしょうか。ただDATE_FORMAT()は、この中では一番遅いのかな……？

CakePHPで書いてみる

ほんじゃあこの3つのSQLを、CakePHPで作ってみることにしましょう。

いつものように、まずはCakePHP2のやり方から見ていきます。

//範囲指定
$params = array(
  'conditions' => array(
    'created >=' => '2015-11-01 00:00:00',
    'created <=' => '2015-11-30 23:59:59',
  ),
);
$data = $this->Post->find('all', $params);

//YEAR()とMONTH()
$params = array(
  'conditions' => array(
    'YEAR(created)' => '2015',
    'MONTH(created)' => '11',
  ),
);
$data = $this->Post->find('all', $params);

//DATE_FORMAT()
$params = array(
  'conditions' => array(
    "DATE_FORMAT(created, '%Y-%m')" => '2015-11',
  ),
);
$data = $this->Post->find('all', $params);

特に難しいことはないです。YEARとかMONTH、DATE_FORMATに関しても、そのまま配列として書けばオッケーです。ただDATE_FORMATは、フォーマットの指定のところにクォーテーションを使う必要があるんで、うっかり見落とすとエラーになっちゃうかも。



じゃあ、次はCakePHP3です。

//範囲指定
$query = $this->Posts->find();
$query->where([
  'created >=' => '2015-11-01 00:00:00',
  'created <=' => '2015-11-30 23:59:59',
]);
$data = $query->all();

//YEAR()とMONTH()
$query = $this->Posts->find();
$query->where([
  'YEAR(created)' => '2015',
  'MONTH(created)'=> '11',
]);
$data = $query->all();

//DATE_FORMAT()
$query = $this->Posts->find();
$query->where([
  "DATE_FORMAT(created, '%Y-%m')" => '2015-11',
]);
$data = $query->all();

「何だよ。CakePHP2と基本的には一緒じゃん？」って思った人。Exactly。確かにその通りです。

でも実は、これだとDATE_FORMAT()の場合のみ、上手く動いてくれないです。syntaxエラーになって、SQLが通りません。

ちょっと理由がよく分からないんですが、$queryをpr()とかで見てみると、コンマの辺りで何やら妙なことになってるんですよね。何でかな？

[0] => Cake\Database\Expression\Comparison Object
  (
    [_value:protected] => '2015-11'
    [_type:protected] => 
    [_operator:protected] => '%y-%m')
    [_field:protected] => DATE_FORMAT(created,
  )

これが何を意味してんだかよく分かってないんで、ここでは何の説明もできないんですが、これを見た人がアドバイスくれるかもしれないから、一応載せときますね。詳しい人いたら教えてください。

とりあえず、フォーマットの部分が「_operator」とかいう方に入っちゃってて、上手くクエリが生成できてないみたいです。

このオブジェクトは、例えばWHERE句の条件が「id = 1」だったら、こんな風になる。

[0] => Cake\Database\Expression\Comparison Object
  (
    [_value:protected] => 1
    [_type:protected] => integer
    [_operator:protected] => =
    [_field:protected] => id
  )

ここから推測すると、_fieldのところが「DATE_FORMAT(created, ‘%Y-%m’)」ってなってくれれば良いってことになるわけだけど……どうやればそうなるか分からん。いや、回避する方法は分かるんだけど、「”DATE_FORMAT(created, ‘%Y-%m’)” => ‘2015-11’」的な書き方でエラーにならなくする方法が、分かりませんでした。

ってことで、もしDATE_FORMAT()を使うのなら、ちょい違う書き方をする必要があります。

エラーを出さずにDATE_FORMAT()を使う

今の僕から提案できる方法は、二つあります。

一つは、配列のキーと要素の形にしないこと。

$query = $this->Posts->find();
$query->where([
  "DATE_FORMAT(created, '%Y-%m') = '2015-11'",
]);
$data = $query->all();

ほとんど一緒だけど、DATE_FORMAT()の部分がキーではなくなっています。これで問題なくSQLが発行されます。

ま、DATE_FORMAT()を使うなら、この書き方で良いでしょう。



もう一つは、キーと要素を逆にするやり方。

すごーく違和感のあるやり方だし、わざわざこんなことしてまでDATE_FORMAT()を使う必要はないと思うんですが、あえてやってみます。

最近気づいたんですけど、SQLのWHERE句のとこって、値とカラムを逆に書いても同じ動きをするんですね。

SELECT * FROM posts WHERE '2015-11-01 00:00:00' <= created AND '2015-11-30 23:59:59' >= created
SELECT * FROM posts WHERE '2015' = YEAR(created) AND '11' = MONTH(created)
SELECT * FROM posts WHERE '2015-11' = DATE_FORMAT(created, '%Y-%m')

こう書いても、同じ動きすんのよ。見慣れてないせいか、ちょい気持ち悪い感じですけどね。

なので、こういうSQLになるように、$query->where()の中身を調整してみます。

$query = $this->Posts->find();
$query->where([
  '2015-11' => "DATE_FORMAT(created, '%Y-%m')",
]);
$data = $query->all();

やったー。これで逆になったぜー。

……と、パッと見は思うんだけど、これだと動かないです。

こう書いた場合、実際に発行されるSQLはこうなる。

SELECT * FROM posts WHERE 2015-11 = 'DATE_FORMAT(created, '%Y-%m')'

普通に考えると、これ構文エラーなはずなんですけど、何でかCakePHPはエラーを出して来ませんでした。こんなふざけたSQLを作ろうとしてる奴には、エラーを出してやる気すら起きねえとか思ってんですかね。

とりあえず、DATE_FORMAT()自体がクォーテーションで囲われちゃっててただの文字列っぽくなってるし、逆に「2015-11」がクォーテーションで囲われてないんで、たぶんこれ、普通に引き算して、2004とかになっちゃってますよね？

何にせよ、これじゃあ期待通りの動きはしません。

じゃあどうすれば良いかって言うと、こうします。

$query = $this->Posts->find();
$date = $query->func()->date_format([
  'created' => 'literal',
  "'%Y-%m'" => 'literal',
]);
$query->where([
  '"2015-11"' => $date,
]);
$data = $query->all();

CakePHP3では、SQLの関数を作成するためのメソッドがあるっぽいです。なので今回は、「$query->func()->date_format()」を使って、DATE_FORMAT()関数を作成しています。

自分で動きを確かめてはいませんが、例えばCONCAT()なんかも、「$query->func()->concat()」って感じで作っていくことになるんだと思います。

literalってのは……何だろう。何ですか？

あと気をつけなきゃいけないのは、「2015-11」がクォーテーションで囲われるようにするとこですね。なので「'”2015-11″‘」みたいな書き方になってます。

ただの文字列なら良いですが、date()関数を使うとき、うっかり忘れちゃうかもしれないので、注意ですね。

$query->where([
  '"'.date('Y-m').'"' => $date,
]);

まあ……やっぱり、あえてここまでする必要はないですよね。

そもそも、値とカラムを逆にするってのは、ありなんですかね？　同じ結果になるとはいえ、最近は足し算の順序が逆なだけで問題にされたりする世の中だし、あまりこういうことはやらない方が良いのかもしれないですね。

一応やれるぞってことだけ、ここでは押さえておきましょう。

SELECTのフィールドでDATE_FORMAT()を使う

さっきまではWHERE句の方でDATE_FORMAT()を使っていましたが、取得するフィールドの方にもこういうのは使えます。

SELECT DATE_FORMAT(created, '%Y-%m') from posts

これをCakePHP2で書く場合。

$params = array(
  'fields' => array("DATE_FORMAT(created, '%Y-%m')"),
);
$data = $this->Post->find('all', $params);

これでオーケーです。

CakePHP3の方も同じにやればとか思うじゃん？

$query = $this->Posts->find();
$query->select(["DATE_FORMAT(created, '%Y-%m')"]);
$data = $query->all();

やっぱりエラーになります。さっきの「$query->func()->date_format()」が必要になります。

$query = $this->Posts->find();
$date = $query->func()->date_format([
  'created' => 'literal',
  "'%Y-%m'" => 'literal',
]);
$query->select(['created_at' => $date]);
$data = $query->all();

注意しなきゃいけないのは、キーを指定する必要があること「$query->select([$date])」だとエラーになっちゃいます。文字列は何でも良いです。今回はcreated_atにしましたが、ようはこういう意味です。

SELECT DATE_FORMAT(created, '%Y-%m') as created_at from posts

asの後ろを何にするかを決めてくれってことですね。この文字列はエンティティデータを使う際に必要になります。

foreach($data as $val) {
  echo $val->created_at;
}

//出力結果
2015-11

いやー、まさかあの一行のために、こんな延々と駄文を書きつづることになろうとは……まあ、どう考えても必要ないことをわざわざやったからなんですけどww

実際のところ、たいした問題ではないんですよ。でもCakePHP2と同じように書いたら動かなかったってなると、じゃあどう書いたら動くのかってのを、知っておかなきゃならないですからね。それが些細なことであってもね。

おそらくですが、他にもSQL関数をそのまま書こうとするとエラーになるケースってあると思うんで、その時はこのパターンの応用で乗り切れるような気がします。



その他のCakePHP3を触ってみましたの記事はこちら
まとめという名の箸休め