DoctrineでSQLの日付関数を使う方法

Symfony Advent Calendar 2015 第一日目の記事です。 http://qiita.com/advent-calendar/2015/symfony

Symfony+Doctrine2でシステムを開発していて、「日付何年のデータだけほしい」、「何年何月のデータだけほしい」という場合。 どう対応していますか？

DateTimeで頑張る

まず、もっとも基本的なやり方として、DBから全件取得したうえで条件を満たすもののみの配列を作るという方法があります。 たとえば、今月ログインしたユーザーを取得したいなら、下記のようになります。

$today = new \DateTime('today');
$activeUsers = array_filter($em->getRepository('AcmeDemoBundle:User')->findAll(), function(\Acme\DemoBundle\Entity\User $user) use ($today){
  return $user->getLastLoginAt()->getYear() === $today->getYear() && $user->getLastLoginAt()->getMonth() === $today->getMonth();
}); // [$user1, $user2, ...]

データ件数が少なければこれでも悪くないのですが、件数が多くなれば巨大なメモリが必要になってしまいます。 そこで、DBから取得する件数を減らすことを考えます。User::$lastLoginAtの保存されるカラムはDATETIME型なので、大小比較ができるはずです。

$today = new \DateTime('today');
$firstDayOfCurrentMonth = new \DateTime($today->format('Y-m-01 00:00:00'));
$lastDayOfCurrentMonth = new \DateTime('...'); // 略。結構面倒な計算が必要です…。
$activeUsers = $em->getRepository('AcmeDemoBundle:User')->createQueryBuilder('u')
    ->where('u.lastLoginAt >= :first_date AND u.lastLoginAt <= :last_date')
    ->setParameter('first_date', $firstDayOfCurrentMonth->format('Y-m-d 00:00:00'))
    ->setParameter('last_date', $lastDayOfCurrentMonth->format('Y-m-d 00:00:00'))
    ->getQuery()
    ->getResult() // [$user1, $user2, ...]
;

…できないわけではありませんが、「今月の最終日」の計算はなかなか面倒です。

Doctrine2でMySQLの日付関数を使う

ところで、MySQLにはDATETIME型のデータから一部の情報を取り出してくれる関数があります。 https://dev.mysql.com/doc/refman/5.6/ja/date-and-time-functions.html

これを使えば、全件取得も面倒な計算も要らなそうです。早速書いて実行してみましょう。

$today = new \DateTime('today');
$activeUsers = $em->getRepository('AcmeDemoBundle:User')->createQueryBuilder('u')
    ->having('year(u.lastLoginAt) = :current_year AND month(u.lastLoginAt) = :current_month')
    ->setParameter('current_year', $today->getYear())
    ->setParameter('current_month', $today->getMonth())
    ->getQuery()
    ->getResult()
; // QueryException [SemanticalError]

…エラーになってしまいました。 Doctrine2のQueryBuilderで使われるのはSQLではなくDQLなので、バックエンドがMySQLであってもMySQLの関数を直接使うことはできないのです。

MySQLの関数をカスタムDQL関数として定義する

MySQLの関数をDQLで使うためには、DQLのカスタム関数として登録する必要があります。 http://doctrine-orm.readthedocs.org/projects/doctrine-orm/en/latest/cookbook/dql-user-defined-functions.html

↑のリンク先にある カスタム関数定義の例 では、Lexer等難しそうな言葉が並んでいますが、今回作りたい日付関数はDQL関数として受け取った引数をそのままMySQLの関数に渡すだけなので、もっと単純に書けます。

下記は、MySQLの YEAR 関数に対応するDQL関数の定義例です。

namespace Acme\DemoBundle\Doctrine;

use Doctrine\ORM\Query\AST\Functions\FunctionNode;
use Doctrine\ORM\Query\Lexer;
use Doctrine\ORM\Query\Parser;
use Doctrine\ORM\Query\SqlWalker;

class Year extends FunctionNode
{
    /**
     * @var \Doctrine\ORM\Query\AST\Node
     */
    public $dateExpression;
    
    public function parse(Parser $parser)
    {
        $parser->match(Lexer::T_IDENTIFIER); // 関数名
        $parser->match(Lexer::T_OPEN_PARENTHESIS); // 開きカッコ
        $this->dateExpression = $parser->ArithmeticPrimary(); // DQL関数に渡された引数
        $parser->match(Lexer::T_CLOSE_PARENTHESIS); // 閉じカッコ
    }
    
    public function getSql(SqlWalker $sqlWalker)
    {
        return 'YEAR('.$this->dateExpression->dispatch($sqlWalker).')';
    }
}

MONTH も、クラス名とgetSql()メソッド内のSQL関数名部分を置き換えるだけで同様に書けます。 このクラスをEntityManagerに登録することで

$config = new \Doctrine\ORM\Configuration();
$config->addCustomDatetimeFunction('month', '\Acme\DemoBundle\Doctrine\Month');
$config->addCustomDatetimeFunction('year', '\Acme\DemoBundle\Doctrine\Year');

$em = EntityManager::create($dbParams, $config);

DQLのカスタム関数としてQueryBuilderで使うことができます。（Symfony standard editionで使う場合は config.yml で設定できます）

早速使ってみましょう。

$today = new \DateTime('today');
$activeUsers = $em->getRepository('AcmeDemoBundle:User')->createQueryBuilder('u')
    ->having('year(u.lastLoginAt) = :current_year AND month(u.lastLoginAt) = :current_month')
    ->setParameter('current_year', $today->getYear())
    ->setParameter('current_month', $today->getMonth())
    ->getQuery()
    ->getResult()
; // [$user1, $user2, ...]

年別・月別等の集計や並べ替えも簡単にできるようになりました。

$today = new \DateTime('today');
$activeUsersCountByMonth = $em->getRepository('AcmeDemoBundle:User')->createQueryBuilder('u')
    ->select('count(u.id) as user_count, month(u.lastLoginAt) as last_login_month')
    ->having('year(u.lastLoginAt) = :current_year')
    ->setParameter('current_year', $today->getYear())
    ->groupBy('last_login_month')
    ->orderBy('last_login_month', 'asc')
    ->getQuery()
    ->getArrayResult()
;  // [['last_login_month' => 1, 'user_count' => 100], ['last_login_month' => 2, 'user_count' => 90], ...]

Doctrine.DateFunctionExtensionを作りました

プロジェクトごとに同じ内容のクラスを書くのが面倒なので、再利用できるライブラリ化しました。 https://github.com/77web/Doctrine.DateFunctionExtension

composerでインストールできます。

$ composer require nanaweb/doctrine-date-function-extension

生のDoctrineを使っている場合はEntityManagerの初期化時に設定してください。

$config = new \Doctrine\ORM\Configuration();
$config->addCustomDatetimeFunction('month', '\Nanaweb\Doctrine\DateFunctionExtension\MySql\Month');
$config->addCustomDatetimeFunction('weekday', '\Nanaweb\Doctrine\DateFunctionExtension\MySql\Weekday');
$config->addCustomDatetimeFunction('year', '\Nanaweb\Doctrine\DateFunctionExtension\MySql\Year');
$config->addCustomDatetimeFunction('weekofyear', '\Nanaweb\Doctrine\DateFunctionExtension\MySql\WeekOfYear');

$em = EntityManager::create($dbParams, $config);

Symfony2で使う場合はconfig.ymlで設定するだけです。

orm:
    # ...
    entity_managers:
        default:
            # ...
            dql:
                datetime_functions:
                    month: \Nanaweb\Doctrine\DateFunctionExtension\MySql\Month
                    weekday: \Nanaweb\Doctrine\DateFunctionExtension\MySql\Weekday
                    year: \Nanaweb\Doctrine\DateFunctionExtension\MySql\Year
                    weekofyear: \Nanaweb\Doctrine\DateFunctionExtension\MySql\WeekOfYear

※ 現時点（2015年12月）で、SQLite3にも対応しています。SQLiteで使いたい場合はnamespaceの MySql の部分を SQLite3 に書き換えてください。

明日は @polidog さんです！