Doctrineでちょっと複雑なSQLを発行する

Doctrineでちょっと複雑なSQLを発行しようとすると躓くことが多いです（個人的に）。
ハマったポイントをいくつかまとめてみたいと思います。

SQLで使える関数が使えない

もちろん特定のRDBMSに依存していないライブラリなので、それぞれのRDBMSで使える関数は使えません。
でもDoctrineで使える関数もいくつかあります。
以下のページにまとまっています。

DQL Functions
https://www.doctrine-project.org/projects/doctrine-orm/en/latest/reference/dql-doctrine-query-language.html#dql-functions

上記のリンクは公式のリファレンスですが、
コードサンプルがないのが悲しいところです。
例えばDATE_ADDを使いたいときは

DATE_ADD(date, days, unit) - Add the number of days to a given date. (Supported units are DAY, MONTH)

この一文を見て使い方を予測します。
今から1ヶ月後は

DATE_ADD(CURRENT_DATE(), 1, 'MONTH')

今から1日前は

DATE_ADD(CURRENT_DATE(), '-1', 'DAY')

※-1にはシングルクォートをつけないと怒られます。

で、Supported units are DAY, MONTHとあるようにYEARには対応していません。
もちろんエラーです。

よく使うものはだいたいあるかなという感じですが、個人的にはMySQLにあるDATE_FORMAT（OracleならTO_CHAR）がないのが辛いです。
DBに登録されている日付から日だけ取得するとか、
時間を切り捨てて日付のみの比較をするようなことができないからです。
次はDATE_FORMATについて書いてみます。

DATE_FORMATが使えない

個人的には結構辛いものがあるのですが、
なにかうまい代替方法はあるものでしょうか・・・？
大抵は渡す引数を工夫したりしますが、どうしてもDATE_FORMATを使いたいときはDQL関数拡張という機能を使います。

ドキュメントはこちら
DQL User Defined Functions
https://www.doctrine-project.org/projects/doctrine-orm/en/2.6/cookbook/dql-user-defined-functions.html

ドキュメントにはサンプルコードがあります。
サンプルコードを見れば関数の追加自体は簡単そうに見えますが、
関数の中身を書くのはちょっと手間そうです。

そんなとき、いろいろな関数をまとめて実装してくださっているコードがgithub上で提供されています（ありがたい）。
DoctrineExtensions
https://github.com/beberlei/DoctrineExtensions

こちらを導入することで、DATE_FORMATも使えるようになります。
手順はサンプルコードにあるので割愛。

FROM句でサブクエリを使いたい

Doctrineではサポートしていません。
過去に要望もあったようですが、却下されたようです。

DDC-2793: Subquery into FROM
https://github.com/doctrine/orm/issues/3542

FROM句でサブクエリを使うようなときはネイティブなクエリを使ってとのことです。

ネイティブなクエリを発行する

本当に複雑なSQLを発行するときは結局はここにたどり着きます。
以下のような感じで発行できます。

$sql = <<<___SQL
SELECT
    *
FROM
    order
WHERE
    order_id = :order_id
___SQL;

$params = ['order_id' => $order_id];
$em = EntityManager::create($dbParams, $config); // EntityManagerのgetはそれぞれのやり方で
$stmt = $em->getConnection()->prepare($sql);
$stmt->execute($params);
$result = $stmt->fetchAll();

ネイティブなクエリを発行したもののEntityと紐付けるのがめんどう

上記のサンプルだと、orderテーブルの中身を全部返しているのでOrderエンティティを返してほしいところです。
そんなときはResultSetMappingBuilderを使います。

$sql = <<<___SQL
SELECT
    *
FROM
    order o
WHERE
    order_id = :order_id
___SQL;

$rsm = new ResultSetMappingBuilder($em);
$rsm->addRootEntityFromClassMetadata('Path\to\Order\Entity', 'o');
$query = $em->createNativeQuery($sql, $rsm);
$query->setParameter('order_id', $order_id);
$orders = $query->getResult();

おわりに

まだまだDoctrineを使いこなしていませんが、いろいろ調べていきたいと思います！