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で頑張ってやってましたけど、
それに見合うかどうかも判断する必要がありますね。
毎回こんな感じでハマってると時間がいくらあっても足りない…。
