DoctrineのSELECT文に関して

DoctrineでSELECT文を上書きする際にハマったのでメモ。

対象テーブルは2テーブル
・account(アカウント一覧)
・friends(友達一覧)
・check(チェックしたユーザー一覧)

以下のようなSQL文をDoctrineから生成しようとしたがうまくいかなかった。

SELECT *, account_id IN (1,2,3,4) AS is_friend FROM friends ORDER BY is_friend DESC;

やりたいことは以下の通り。
checkテーブルのデータを各種条件を指定して取得し、
そのデータを友達かどうかをキーにしてソートする。

最初に書いていたのはこんな感じ。

$query = Doctrine_Core::getTable('Check')
           ->select('*, account_id IN (1,2,3,4) as is_friend')
           ->where('delete_flg = ?', 0)
           ->limit(10)
           ->offset(0)
           ->orderBy('is_friend DESC');

SELECT内に書かれている「account_id IN (1,2,3,4)」の部分がうまく解釈されずに、
本来であればいる「1」かいないか「0」の2種類の結果がis_friendに格納されて返ってくるはずなのだが、
is_friendにはcheckテーブルに含まれるaccount_idが入って返ってきてしまっていた。

原因はどうやらparseSelectの中でやっている処理っぽい。
さすがにこの部分を書き換えるわけにはいかないので、
どうにかできないかと色々と試してはみたのだが、結局わからず終い。

どうしたものかと思っていたのだが、
だったら別のSQLの書き方で同じ結果を取得できるようにすればいいんじゃないかと思った。

JOINとかしてやる方法はパフォーマンス的に悪すぎるので却下。
なんとかSQL一発で取れる方法を探していたらありましたよ。

「CASE」

これ使えばいけるかもと思って、早速MySQLでコマンド叩いてみた。

SELECT *, CASE WHEN account_id IN (1,2,3,4) THEN 1 END AS is_friend FROM friends ORDER BY is_friend DESC;

いけた！
全く同じ結果ではないですが、is_friendに1かNULLが入る感じの結果が返ってきました。
CASEもう一個書けばNULLのとこを0返すようにできますが、必要ないと思ったのでやっていません。

で、今度はこれをちゃんとDoctrineが作ってくれるのかってとこです。

$query = Doctrine_Core::getTable('Check')
           ->select('*, CASE WHEN account_id IN (1,2,3,4) THEN 1 END AS is_friend')
           ->where('delete_flg = ?', 0)
           ->limit(10)
           ->offset(0)
           ->orderBy('is_friend DESC');

これを実行してみると、SQLのSyntaxエラーに…。
ググってみたら、意外とさっくり出てきた。
どうやらCASE～ENDの前後に括弧をつけるといけるらしい。
早速試してみた。

$query = Doctrine_Core::getTable('Check')
           ->select('*, (CASE WHEN account_id IN (1,2,3,4) THEN 1 END) AS is_friend')
           ->where('delete_flg = ?', 0)
           ->limit(10)
           ->offset(0)
           ->orderBy('is_friend DESC');

出来た！！！

エラーもなく、希望通りのレスポンスが返ってきました。
やはり複雑なSQLになるとDoctrineを使うのも一苦労ですね…。
Doctrineのキャッシュを使いたいがためにDoctrineで頑張ってやってましたけど、
それに見合うかどうかも判断する必要がありますね。
毎回こんな感じでハマってると時間がいくらあっても足りない…。

doctrineでWhereとandWhereは書き分ける必要はない

doctrine使っていて、条件によってWhereを付加する場合に、WhereとandWhereを書き分けなきゃいけないと思ってたけど、
違うみたい。

$qeury = Doctrine_Query::create()->from('ModelName');
$qeury->andWhere('hoge = ?', $hoge);
$qeury->andWhere('fuga = ?', $fuga);

と、こうやって書いても問題なく動く。
Propelと違ってわかりずらいと思ったけど、まぁ解決したのでよしとする。

doctrineで実行されるSQLを事前に確認する方法

getSqlQueryで確認できる。

$q = Doctrine_Query::create();
$q->from('TableName');
echo $q->getSqlQuery();

doctrineでcreated_atとupdated_atに自動で日付を入れる方法

今回初めてdoctrineを使っていて、作成日と更新日に時間が自動で入っていないことに気づいた。
Propelの場合は何もしなくても、勝手に入れてくれていたが、doctrineはそうではないらしい。
調べてみるとschema.ymlに以下のような記述を追加すればできるみたい。

モデル名がTableName、テーブル名がtable_nameに対して自動で日付を入れる場合
config/doctrine/schema.ymlを開いて以下のように「actAs: { Timestampable: ~ }」の行を追加する。

TableName:
  actAs: { Timestampable: ~ }
  connection: doctrine
  tableName: table_name
  columns:
    table_id:
      type: integer(4)
      fixed: false
      unsigned: false
      primary: true
      autoincrement: true
　　　　　　：
　　　　　　：

といった感じ。
Timestampableの値が「～」ってなってて、なんだこれと思ったけど、追加してみるとちゃんと動いた。
追加した後はモデルを再生成する。
そうするとBaseTableNameクラスのsetUpという関数の中に以下の記述が追加される。

$timestampable0 = new Doctrine_Template_Timestampable();
$this->actAs($timestampable0);

これで変更作業は完了。
あとはsaveメソッドで保存したものに関しては作成日付と更新日付が自動で挿入されるようになる。

doctrineで生成するschema.ymlに関して

symfonyのタスクでデータベース上に保存されているデータをfixture形式でダンプを取ってくれるタスクがあり、
それを実行してみた。

symfony doctrine:data-dump

すると、

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'v.id' ～

v.idってカラムがないらしい。
そもそもidなんてカラム作ってないのになんでこんなことになってるんだと思って調べてみたら、
エラーが出たのはViewの部分の記述だった。
そこで、schema.ymlを見てみた。
中を見てみると

VHoge:
  connection: doctrine
  tableName: v_hoge
  columns:
    id:
      type: integer(8)
      autoincrement: true
      primary: true
    fuga_id:
      type: integer(4)
      fixed: false
      unsigned: true
      primary: true
      default: '0'
      notnull: true
      autoincrement: false

idってカラムがありました･･･。
なんでこんなカラムが追加されてるんだと思いながらも、削除してみた。
モデルも再生成して、もっかい実行。
同じエラーが･･･。
カラムは削除したはずなのになーと思いながらも、primaryがtrueになっているカラムがないといけないのではないかと思い、fuga_idのprimaryをtrueにして、モデルを再生成して実行してみたら成功しました。

どうやらschema.ymlには必ずprimaryがtrueになっているカラムが1つないとエラーになるみたいです。
あとでソースを追って、ちゃんとした原因を突き止めよう。

doctrineで生成されるBase～.class.phpと～.class.phpと～Table.class.phpの棲み分けについて

doctrineでmodelを生成すると1つのテーブルに対して以下の3ファイルが生成されます。

例）テーブル名がhogeだった場合に作成されたモデルクラスの場合

base/BaseHoge.class.php
Hoge.class.php
HogeTable.class.php

これらのファイルにはそれぞれ違った役割があり、
それを意識することで綺麗なコードが書けるようになると思います。

BaseHoge.class.phpに関して

こちらはbuildするたびに書き換えられてしまうので、触らないようにしましょう。
もし修正してもbuild-modelのコマンドを実行した時点で新しいファイルに差し替わってしまいます。

HogeTable.class.phpに関して

このクラス内ではテーブルからレコードを取得する処理を記述する。
ようは、テーブルに対して行う処理に関しては全てここに記述する。

Hoge.class.phpに関して

HogeTable.class.phpで取得したレコードに対して処理を実行する場合はこちらに記述する。
つまり、テーブルにレコードを直接取得するような記述は書かない。
あくまでHogeTable.class.phpで取得してきた取得結果に対しての処理を記述するのがこっち。

以上が棲み分けになる。
なぜこのようになるかというのは、継承関係が以下のようになっているからってことみたいです。
・Doctrine_Record > sfDoctrineRecord > Hogeクラス
・Doctrine_Table > HogeTableクラス

・参考文献
http://www.kazu.tv/blog/archives/000954.html