CakePHPのモデルからSQLを外出しする

CakePHPを使って開発をしていると、自分でSQLを書く機会が劇的に減ると思います。簡単なSQLであればモデルのメソッドでほとんど記述できる。でも、やっぱり難しいSQLを実行したいとなると自分でSQLを書いてそれを実行することが多いですよね。そんな時どうしてますか？

SQLというのは通常、結構行数をくいます。たとえば

$query = <<<EOD
    SELECT
        Post.id,
        Post.subject,
        Post.created
    FROM
        posts Post
    WHERE
        Post.created <= 'YYYY-MM-DD'
EOD;
$results = $this-<query($query);

こんな感じの簡単なSQLでもそれなりに行数が。。さて、簡単なSQLならまだいいけど、こういう風に直接SQLを書いて実行したい場合は、もっと複雑なSQLになることが多いです。たとえばUNION句を使って結合していたり、条件部分にEXIST句を利用していたり、サブクエリを利用していたりする場合。そうすると、これとは比べ物にならないくらい大きな行数になって、時にはテキストエディタの1画面内に入り切らないことも。。。

モデル内に直接SQLを書くとモデルが肥大化してメンテナンスしにくくなってしまうので、SQLを外部に書きだして、それを読み込むことによってモデルの中身を軽量化を目指します。

まず、AppModelにこんな感じで関数を定義。

/**
 * SQLファイルを読み込む
 *
 * @param string $fileName SQLファイル名
 * @param array  $params   パラメータ
 * @param array  $escape   シングルクォーテーションをエスケープしないパラメータのキー
 * @return string SQL
 */
function sql($fileName, $params = array(), $escape = array())
{
 // ファイル名に拡張子まで指定されていれば取り除く
 if (substr($fileName, -4) == ".sql") {
  $fileName = substr($fileName, strlen($fileName) - 4);
 }
 
 $query = "";
 
 // ファイルが存在することを確認
 $paths = array(
  MODELS . "sql" . DS . Inflector::underscore($this->name) . DS . $fileName . ".sql", // 自モデルのディレクトリ
  MODELS . "sql" . DS . $fileName . ".sql"                                            // 共通ディレクトリ
 );
 $sqlFilePath = "";
 foreach ($paths as $value) {
  if (file_exists($value)) {
   $sqlFilePath = $value;
   break;
  }
 }
 
 if (!empty($sqlFilePath)) {
  if (empty($escape)) {
   // エスケープ除外対象がなければすべての変数についてシングルクォーテーションをエスケープする
   array_walk($params, array($this, "escapeQuote"));
  }
  else {
   // エスケープ除外対象があれば、配列から除外する
   $tmp = $params;
   foreach ($params as $key => $value) {
    if (in_array($key, $escape)) {
     unset($tmp[$key]);
    }
   }
   
   // シングルクォーテーションをエスケープする
   array_walk($tmp, array($this, "escapeQuote"));
   
   // エスケープしたものとそれ以外のものをマージする
   $params = array_merge($params, $tmp);
  }
  
  // パラメータを展開
  extract($params, EXTR_OVERWRITE);
  
  // ファイルからSQLを読み込む
  ob_start();
  include $sqlFilePath;
  $query = ob_get_clean();
 }
 else {
  $this->log("sqlファイルが見つかりません。" . $sqlFilePath);
 }
 
 return $query;
}

/**
 * シングルクォーテーションをエスケープする
 */
function escapeQuote(&$value, $key)
{
    if (is_string($value)) {
        $value = str_replace("'", "''", $value);
    }
    else if (is_array($value)) {
        array_walk($value, array($this, __FUNCTION__));
    }
}

で、直書きSQLを呼び出したいときは、app/models/sql/モデル名/以下にSQLファイルを作る。たとえば、app/models/sql/post/list.sqlというファイルにさっきのSQLを書いたとすると

$query = $this->sql("list.sql");
$results = $this->query($query);

と、たったこれだけに！

パラメータを渡したい場合は、2つ目の引数にキーと値の連想配列を渡してあげる。ちょうどビューのエレメントを呼ぶ時と同じようなやり方です。

モデルがSQLで埋まらないように、整理しましょう！