[Symfony] Doctrineで「グループの中で最大なエンティティ」のリストを取得する

この記事は、2020-04-25に別のブログ媒体に投稿した記事のアーカイブです。

Doctrineで

• ある基準でグループ化して
• 各グループの中であるカラムの値が最大なものだけを
• リストとして取得する

というのが意外と難しかったのでメモに残しておきます。

実際に動かせるサンプルをGitHubに上げたので、あわせて参考にしてみてください。

https://github.com/ttskch/doctrine-sub-query-sample

要件

例として以下の要件を考えます。

• 「投稿」と「コメント」というエンティティがある
• 投稿は複数のコメントを持つ
• コメントには「投稿日時」というプロパティがある
• このとき、各投稿のコメントのうち、投稿日時が最新のものだけをリスト化して取得したい

PHP側でやってしまえばとても簡単です。

$newestComments = [];

foreach ($posts as $post) {
    $newestComment = null;

    foreach ($post->getComments() as $comment) {
        if (!$newestComment || $newestComment->getCreatedAt() <= $comment->getCreatedAt()) {
            $newestComment = $comment;
        }
    }

    $newestComments[] = $newestComment;
}

こんな感じで実装できますね。

でも、これだとレコード数が多いときに計算量がやばいので、できればDBレイヤーで解決したいです。

SQLレベルで考える

まずはSQLレベルで考えてみましょう。

結論としては、以下のようなSQLで（ちょっと強引ですが）目当てのデータを抽出することができます。

SELECT
    c.*
FROM
    comment c
WHERE
    c.created_at = (
        SELECT
            max(c2.created_at)
        FROM
            comment c2
        WHERE
            c2.post_id = c.post_id
    )
;

• コメント一覧を作成日時でフィルタリングして取得
• 作成日時は「同じ投稿を親に持つコメントの中で最大の値」をサブクエリで取得して、それと一致するものを抽出

という方法です。

お察しのとおり、たまたま偶然投稿日時が秒単位で同じなコメントがあると間違って取得されてしまいます😓が、おおよそのケースで大丈夫だろうということで、今回はこの方法でやってみたいと思います。

もっとエレガントな解法を思いつく方はぜひ Twitter 等でフィードバックいただけると嬉しいです！

QueryBuilderでSQLを組み立てる

先ほどのSQLをDoctrineのQueryBuilderを使って組み立てていきます。

一旦DQLレベルで考えてみると、欲しいクエリは以下のようなものになります。

SELECT c FROM App\Entity\Comment c WHERE c.createdAt = (SELECT max(c2.createdAt) FROM App\Entity\Comment c2 WHERE c2.post = c.post)

これがなかなか難儀したのですが、結論としては以下のようなコードで実現できます。

class CommentRepository extends ServiceEntityRepository
{
    // ...

    public function findLastCommentsGroupedByPost()
    {
        $expr = $this->getEntityManager()->getExpressionBuilder();

        // sub query to select comments have latest createdAt
        $subQueryDQL = $this->createQueryBuilder('c2')
            ->select('max(c2.createdAt)')
            ->andWhere('c2.post = c.post')
            ->getDQL()
        ;

        return $this->createQueryBuilder('c')
            ->andWhere($expr->eq('c.createdAt', sprintf('(%s)', $subQueryDQL)))
            ->getQuery()
            ->getResult()
        ;
    }
}

サブクエリの結果をメインクエリのWHERE句に使いたい場合には \Doctrine\ORM\Query\Expr を使う必要がある というところがポイントです。

また、サブクエリを展開する際に sprintf('(%s)', $subQueryDQL) と () で括っていることにも注意してください。

DQLで言う

WHERE c.createdAt = (SELECT max(c2.createdAt) ...)

の () です。この () がないと正しいSQLが作れず以下のようなSyntaxエラーになります。

[Syntax Error] line 0, col 55: Error: Expected Literal, got 'SELECT'

動かしてみる

以下のようなデータを投入して、

INSERT INTO post (id, content, created_at) VALUES
(1, "post1", "2020-01-01"),
(2, "post2", "2020-01-01"),
(3, "post3", "2020-01-01");

INSERT INTO comment (id, content, post_id, created_at) VALUES
(1, "older comment for post1", 1, "2020-01-01")
(2, "newer comment for post1", 1, "2020-01-02")
(3, "older comment for post2", 2, "2020-01-03")
(4, "newer comment for post2", 2, "2020-01-04")
(5, "older comment for post3", 3, "2020-01-05")
(6, "newer comment for post3", 3, "2020-01-06");

以下のようなコントローラを用意して、

public function index(CommentRepository $commentRepository)
{
    $comments = $commentRepository->findLastCommentsGroupedByPost();

    return new JsonResponse($comments);
}

実際にデータを取得してみましょう。

各投稿にそれぞれ2つずつコメントがありますが、「同じ投稿に対するコメントの中で作成日時が最新のコメントのみ」を取得しているはずなので、

idが偶数の、contentが newer comment for ... となっているコメントのみが出力されること

が期待値ですね。

では、動かしてみます。

$ curl localhost:8000 | jq .
[
  {
    "id": 2,
    "post_id": 1,
    "content": "newer comment for post1",
    "created_at": {
      "date": "2020-01-02 00:00:00.000000",
      "timezone_type": 3,
      "timezone": "UTC"
    }
  },
  {
    "id": 4,
    "post_id": 2,
    "content": "newer comment for post2",
    "created_at": {
      "date": "2020-01-04 00:00:00.000000",
      "timezone_type": 3,
      "timezone": "UTC"
    }
  },
  {
    "id": 6,
    "post_id": 3,
    "content": "newer comment for post3",
    "created_at": {
      "date": "2020-01-06 00:00:00.000000",
      "timezone_type": 3,
      "timezone": "UTC"
    }
  }
]

バッチリ期待どおりですね！🙌

少しだけ要件を複雑にしてみたバージョン

最後におまけで、

• コメントには「公開状態」というフラグがあり、これが true なコメントしか出力したくない
• 指定した投稿に対するコメントの中で最新のものを出力する、という機能もほしい

という要件を足してみましょう。

この場合、リポジトリのコードは以下のようになるでしょう。

public function findLastCommentsGroupedByPost(Post $post = null)
{
    $expr = $this->getEntityManager()->getExpressionBuilder();

    // sub query to select comments have latest createdAt
    $subQueryDQL = $this->createQueryBuilder('c2')
        ->select('max(c2.createdAt)')
        ->andWhere('c2.post = c.post')
        ->andWhere('c2.published = 1')
        ->getDQL()
    ;

    $qb = $this->createQueryBuilder('c')
        ->andWhere($expr->eq('c.createdAt', sprintf('(%s)', $subQueryDQL)))
    ;

    if ($post) {
        $qb
            ->andWhere('c.post = :post')
            ->setParameter('post', $post)
        ;
    }

    return $qb->getQuery()->getResult();
}

コントローラを適当に用意して動かしてみます。

$ curl localhost:8000 | jq .
[
  {
    "id": 2,
    "post_id": 1,
    "content": "newer comment for post1",
    "created_at": {
      "date": "2020-01-02 00:00:00.000000",
      "timezone_type": 3,
      "timezone": "UTC"
    },
    "published": true
  },
  {
    "id": 3,
    "post_id": 2,
    "content": "older comment for post2",
    "created_at": {
      "date": "2020-01-03 00:00:00.000000",
      "timezone_type": 3,
      "timezone": "UTC"
    },
    "published": true
  }
]

$ curl localhost:8000/1 | jq .
[
  {
    "id": 2,
    "post_id": 1,
    "content": "newer comment for post1",
    "created_at": {
      "date": "2020-01-02 00:00:00.000000",
      "timezone_type": 3,
      "timezone": "UTC"
    },
    "published": true
  }
]

$ curl localhost:8000/2 | jq .
[
  {
    "id": 3,
    "post_id": 2,
    "content": "older comment for post2",
    "created_at": {
      "date": "2020-01-03 00:00:00.000000",
      "timezone_type": 3,
      "timezone": "UTC"
    },
    "published": true
  }
]

$ curl localhost:8000/3 | jq .
[]

要件どおり動いていますね👍

まとめ

• Doctrineで「グループの中で最大なエンティティ」のリストを取得したい場合、サブクエリでグループ内の最大値を取得して、 \Doctrine\ORM\Query\Expr を使ってメインクエリのWHERE句でサブクエリを使えばよい

参考リンク

• http://labs.timedia.co.jp/2014/10/selecting-max-record-in-group-by.html
• https://riptutorial.com/ja/symfony2/example/23132/
• https://stackoverflow.com/questions/6637506/doing-a-where-in-subquery-in-doctrine-2
• https://stackoverrun.com/ja/q/12099025