SymfonyでDQLを拡張してMysqlの関数を使えるようにする

# 概要
Symfony + EC-CUBEでDQLを拡張する方法を学んだのでまとめました。

# 動作確認環境
```
EC-CUBE 4.0.3
Symfony 3.4.26
PHP 7.3
MySQL 5.7
```

# DQLの拡張方法
## DoctrineExtensionsライブラリを導入する
今回は、キーワードで検索時に照合順序を指定して検索することが課題でした。
なので、MysqlのCOLLATEが利用できればよかったのでDoctrineExtensionsライブラリを利用しました。他にもライブラリが存在するでしょうが、用途や実現したいことによって使い分ければよいと思います。

```
composer require beberlei/doctrineextensions
```

使いたい関数があるかどうかは、githubを参照してください。
[DoctrineExtensions](https://github.com/beberlei/DoctrineExtensions)

## EntityManagerに利用したいMySQLの関数を設定する
クエリを生成する前にEntityManagerに利用したい関数を設定する。

```php
$em = $this->getEntityManager();
$config = $em->getConfiguration();
$config->addCustomStringFunction('COLLATE', Collate::class);
```

## 設定したSQL関数を利用する
collateだと次のように
`COLLATE({検索対象のカラム}, {指定したいcollation})` というように呼び出すことで照合順序を指定できる。

```php
if (isset($searchData['code'])) {
    $queryBuilder->andWhere('COLLATE(p.code, utf8_unicode_ci) like :type_code')
        ->setParameter('code', '%' . $searchData['code'] . '%');
}
```

初めはDQL上でCOLLATEの呼び出し方が分からず、次のように書いてsyntax errorで苦しみました・・・。

```php
// これはsyntax error
$queryBuilder->andWhere('p.code like :type_code COLLATE utf8_unicode_ci');
    
// これもsyntax error
$queryBuilder->andWhere('p.code COLLATE utf8_unicode_ci like :type_code')
```

訳が分からず検索しまくっていたら、似たような処理を行っている人が

> COLLATE( field , collation ) 

と呼び出していたので、やっとDQLの書き方が分かりました。
[php - Doctrine DQLクエリ内でCOLLATEを使用する(Symfony2)](https://ja.coder.work/so/php/1176397)
COLLATE以外の関数も、DQL上では基本的に`関数名(引数)`として呼び出すのかもしれません。(他のSQL関数は利用しなかったため未検証)


## サンプルコード

```php
<?php

namespace Xxx\Repository;

use DateTime;
use Doctrine\ORM\QueryBuilder;
use DoctrineExtensions\Query\Mysql\Collate;
use Eccube\Repository\AbstractRepository;
use Entity\Product;
use Symfony\Bridge\Doctrine\RegistryInterface;

class ProductRepository extends AbstractRepository
{
    public function __construct(RegistryInterface $registry)
    {
        parent::__construct($registry, Product::class);
    }

    public function getQueryBuilderBySearchData(array $searchData): QueryBuilder
    {
        // 今回は、検索時にCOLLATEを指定するためCOLLATE関数を指定
        $em = $this->getEntityManager();
        $config = $em->getConfiguration();
        $config->addCustomStringFunction('COLLATE', Collate::class);

        $queryBuilder = $this->createQueryBuilder('p')
            ->orderBy('p.typeCode', 'ASC');

        if (isset($searchData['code'])) {
            $queryBuilder->andWhere('COLLATE(p.code, utf8_unicode_ci) like :type_code')
                ->setParameter('code', '%' . $searchData['code'] . '%');
        }

        if (isset($searchData['name'])) {
            $queryBuilder->andWhere('COLLATE(p.name, utf8_unicode_ci) like :name')
                ->setParameter('name', '%' . $searchData['name'] . '%');
        }

        return $queryBuilder;
    }
}
```

# 備考 照合順序について
簡単にいうと、
半角・全角、小文字・大文字、かな・カナを区別せずに検索したり、反対に、区別して検索したいときに指定するもの。
カラム自体に定義することもできるし、クエリを投げるときにMySQLのCOLLATE関数で指定することもできる。

日本語に関しては、MySQLではこの3つしかないらしい。
- utf8_bin
- utf8_general_ci
- utf8_unicode_ci

詳しくは他の記事を参考にしてください。
- [【MySQL】照合順序とは？](https://qiita.com/kazu56/items/6af85ffcf8d3954455ad)
- [MySQLの照合順序](https://qiita.com/Vit-Symty/items/159c27d7d62c78ee9ce7)
- [MariaDB(MySQL)の照合順序の話](https://blog.ver001.com/mariadb_collation/)

# 参考記事
- [Symfony2【Doctrine DQL関数拡張】MySQL(またはPostGreSQL)のNOW,ROUND,FLOOR,GROUP_CONCAT,REPLACE等の関数を使用する](https://webcode-lab.com/topics/blogid/24/)
  - DQL関数を拡張したことがなかったので、こちらの記事は大変参考になりました。
- [php - Doctrine DQLクエリ内でCOLLATEを使用する(Symfony2)](https://ja.coder.work/so/php/1176397)