CakePHP2.x系で直接SQLを記載して実行する方法

2017/11/17 CakePHP

CakePHP2.x系ではORMが便利で直接SQLを記載する事はあまりありませんが、複雑になるなど、どうしても直接SQLを記載する時があると思います。

以下でいけます。fetchAllです。

<?php
  public function querySelectCommentsReplies($torettaId) {
    $db = $this->getDataSource();
    $sql = "SELECT
              TorettasComment.id,
              TorettasComment.user_id,
              TorettasComment.comment,
              TorettasComment.created,
              TorettasCommentsReply.id,
              TorettasCommentsReply.user_id,
              TorettasCommentsReply.torettas_comment_id,
              TorettasCommentsReply.reply,
              TorettasCommentsReply.created
            FROM
              torettas_comments AS TorettasComment
            LEFT JOIN
              torettas_comments_replies AS TorettasCommentsReply
              ON TorettasComment.id = TorettasCommentsReply.torettas_comment_id
            WHERE
              TorettasComment.toretta_id = ?
    ";
    $result = $db->fetchAll($sql, array($torettaId));

    return $result;
  }

public function querySelectCommentsReplies($torettaId) {

$db = $this->getDataSource();

$sql = "SELECT

TorettasComment.id,

TorettasComment.user_id,

TorettasComment.comment,

TorettasComment.created,

TorettasCommentsReply.id,

TorettasCommentsReply.user_id,

TorettasCommentsReply.torettas_comment_id,

TorettasCommentsReply.reply,

TorettasCommentsReply.created

FROM

torettas_comments AS TorettasComment

LEFT JOIN

torettas_comments_replies AS TorettasCommentsReply

ON TorettasComment.id = TorettasCommentsReply.torettas_comment_id

WHERE

TorettasComment.toretta_id = ?

$result = $db->fetchAll($sql, array($torettaId));

return $result;

}