DQLではMySQLのDATE_FORMATが使えないので生クエリを発行してみた。

例えばブログなどの投稿記事を男女別、日別でカウントを取りたいとすると
雰囲気下記のような感じになる。

// ファイルの先頭で宣言
use Hoge\FugaBundle\Entity\Post;
use Doctrine\ORM\Query\ResultSetMapping;

...
// 関数内
$em = $this->getDoctrine()->getManager();
// 何日前の投稿分まで集計するか
$pastRange = date('Y-m-d', strtotime('-16 days'));

$sql = "select date_format(p.createdAt, '%Y-%m-%d') as date, p.sex as sex, count(p.id) as cnt from posts as p where p.deletedAt is null and p.status in (1, 2) and p.createdAt >= ? 

group by date, sex";

$rsm = new ResultSetMapping();
$rsm->addEntityResult('HogeFugaBundle:Post', 'p');
$rsm->addScalarResult('date', 'date');
$rsm->addScalarResult('sex', 'sex');
$rsm->addScalarResult('cnt', 'cnt');

$query = $em->createNativeQuery($sql, $rsm);
$query->setParameter(1, $pastRange);

// FOSRestBundleを用いてjsonでレスポンスを返却する。
$view = $this->view($query->getResult(), 200)->setFormat('json');
return $this->handleView($view);

これでDQLに無い関数も利用出来るようになった。

おまけ。曜日、時間帯、男女別ごとにカウントを取るSQL。

$sql = "select date_format(p.createdAt, '%w') as day, date_format(p.createdAt, '%H') as hour, p.sex as sex, count(p.id) as cnt from posts as p where p.deletedAt is null and p.status 

in (1, 2) and p.createdAt >= ? group by day, hour, sex";

$rsm = new ResultSetMapping();
$rsm->addEntityResult('HogeFugaBundle:Post', 'p');
$rsm->addScalarResult('day', 'day');
$rsm->addScalarResult('hour', 'hour');
$rsm->addScalarResult('sex', 'sex');
$rsm->addScalarResult('cnt', 'cnt');

うん。楽しい。

createNativeQuery()の詳細は下記公式を参照されたし。

http://doctrine-orm.readthedocs.org/en/latest/reference/native-sql.html