はじめに

CakePHP 4 でデータを取得する際には、基本的には生クエリは書かず ORM を使用します。

ORM を使うことで保守性が良くなったりするのですが、クエリが複雑な場合には SQL を確認したいことがあります。

CakePHP ではデバッグモードにしていると、DebugKit の SqlLog パネルで SQL を確認することができます。しかし SQL でエラーが出る場合には、そのコード内で SQL 取得や表示が必要になります。

そこで今日は CakePHP 4 でデータを取得する際の SQL をコード内で取得、表示する方法を紹介します。

動作には DebugKit が必要となります。基本的に DebugKit はインストール済みだと思いますが、デバッグモードにする必要があるのでご注意ください。
（app_local.php で debug を true にする）

目次

1. 下準備
2. debug() を使う
3. Query::sql() を使う
4. sql() と sqld() 【オススメ】
5. おわりに

1. 下準備

今回使用したデータベースとコントローラです。
articles テーブルのデータとモデルは無しで大丈夫です。

CREATE TABLE articles (
  id int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  status tinyint(3) UNSIGNED NOT NULL DEFAULT 1,
  date date DEFAULT NULL,
  title varchar(255) DEFAULT NULL,
  body text DEFAULT NULL,
  created datetime NOT NULL,
  modified datetime NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

<?php
declare(strict_types=1);

namespace App\Controller;

class SamplesController extends AppController
{
}

2. debug() を使う

all() や first() をする前に debug() することで SQL などの情報を表示できます。

public function index()
{
    $this->loadModel('Articles');
    $query = $this->Articles->find()
        ->order(['date' => 'DESC'])
        ->where(['status' => 1]);

    debug($query);
    exit;
}

実行結果は下記のような感じです（一部省略しています）。
様々な情報が取得できますが、単に SQL を確認したい場合には情報過多のように思います。

object(Cake\ORM\Query) {

  '(help)' => 'This is a Query object, to get the results execute or iterate it.',
  'sql' => 'SELECT Articles.id AS Articles__id, ... FROM articles Articles WHERE status = :c0 ORDER BY date DESC',
  'params' => [
    ':c0' => [
      'value' => (int) 1,
      'type' => 'tinyinteger',
      'placeholder' => 'c0'
    ]
  ],
  'defaultTypes' => [
    'Articles__id' => 'integer',
    'Articles.id' => 'integer',
    'id' => 'integer',
    ...
  ],
  'decorators' => (int) 0,
  ...
  'repository' => object(Cake\ORM\Table) {

    'registryAlias' => 'Articles',
    'table' => 'articles',
    ...

  }

}

3. Query::sql() を使う

メソッドチェーンで Query::sql() を使うことで SQL を取得できます。
sql() の結果にはバインドしている値が含まれませんが、
Cake\Database\ValueBinder::bindings() で得ることができます。

first() や all() の後に sql() をつなげるとエラーになるのでご注意ください。
また bindings() は sql() の後に実行する必要があります。

public function index()
{
    $this->loadModel('Articles');
    $query = $this->Articles->find()
        ->order(['date' => 'DESC'])
        ->where(['status' => 1]);

    // SQL 取得
    //（バインド箇所はプレースホルダーになる）
    debug($query->sql());

    // バインドしたパラメータを表示
    // (sql() の前に実行すると取得できない)
    debug($query->getValueBinder()->bindings());

    exit;
}

\src\Controller\SamplesController.php (line 17)
'SELECT Articles.id AS Articles__id, ... FROM articles Articles WHERE status = :c0 ORDER BY date DESC'

\src\Controller\SamplesController.php (line 21)
[
  ':c0' => [
    'value' => (int) 1,
    'type' => 'tinyinteger',
    'placeholder' => 'c0'
  ]
]

こちらは SQL を返す関数なので、ログに残したい場面で使いやすいかもしれませんね。

4. sql() と sqld() 【オススメ】

sql() は SQL を整形して表示する関数です。バインドした値が最初から含まれていて、また debug() などを書かなくて済むので、開発中のデバッグシーンで使い勝手がいいです。

sqld() は sql() + exit で、そこで処理を停止します。こちらも exit; を省略できるので手間を軽減できます。

public function index()
{
    $this->loadModel('Articles');
    $query = $this->Articles->find()
        ->order(['date' => 'DESC'])
        ->where(['status' => 1]);

    echo '<h1>sql</h1>';
    sql($query);

    echo '<h1>sqld</h1>';
    sqld($query);

    // sqld() で 処理が止まるため、以下は実行されない
    echo '<h1>これは表示されません</h1>';
}

ブラウザでは以下のように表示されます（sqld の結果は省略しています）。
太字やインデントなどが付加されていて見やすいです。

余談ですが、sql() は CakePHP 4.x の Cookbook にあるのですが、
sqld() は DebugKit のドキュメントでみつけました。

Debugging Queries and ResultSets (CakePHP 4.x Cookbook)

https://book.cakephp.org/4/en/orm/retrieving-data-and-resultsets.html#debugging-queries-and-resultsets

Helper Functions (CakePHP DebugKit 4.x Cookbook)

https://book.cakephp.org/debugkit/4/en/index.html#helper-functions

5. おわりに

単純な取得の場合には SQL をデバッグすることは少ないかもしれませんが、UNION やサブクエリ、計算などを行う際には役立ちます。

僕もクエリが複雑になるときにはよく SQL を確認するのですが、しばらくの間 sql() や sqld() を知らず、メソッドチェーンの Query::sql() と debug() の組み合わせでやっていたので大変でした（汗）

sql() と sqld() は覚えておくとデバッグ作業の負担が減り、作業効率が上がると思いますので、是非使ってみてください。