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

EntityManagerに利用したいMySQLの関数を設定する

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

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

設定したSQL関数を利用する

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

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

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

// これは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)
COLLATE以外の関数も、DQL上では基本的に関数名(引数)として呼び出すのかもしれません。(他のSQL関数は利用しなかったため未検証)

サンプルコード

<?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】照合順序とは？
• MySQLの照合順序
• MariaDB(MySQL)の照合順序の話

参考記事

• Symfony2【Doctrine DQL関数拡張】MySQL(またはPostGreSQL)のNOW,ROUND,FLOOR,GROUP_CONCAT,REPLACE等の関数を使用する
  • DQL関数を拡張したことがなかったので、こちらの記事は大変参考になりました。
• php - Doctrine DQLクエリ内でCOLLATEを使用する(Symfony2)