🎻

[Symfony][Doctrine] COUNT()やCONCAT()の結果でORDER BYする方法

に公開
PHP
Symfony
Doctrine
tech

はじめに

Symfony Advent Calendar 2020 の2日目の記事です！🎄🌙

昨日は @77web さんの12/3-12/4 Symfony World 2020が開催されます！ でした✨

ちなみに、僕はよく TwitterにもSymfonyネタを呟いている ので、よろしければぜひ フォローしてやってください🕊🤲

やりたいこと

エンティティの一覧画面を、所有する子エンティティの数の順にソートしたいことってよくありますよね。

他にも、例えばエンティティの文字列表現（ __toString() の結果）を一覧画面に表示していて、その文字列でソートしたい、みたいなこともたまにはあるかもしれません。

どちらもSQLレイヤーで COUNT() や CONCAT() を実行した結果に対して ORDER BY をしたいという話なのですが、これをDoctrineで実施する方法について説明します。

やり方

COUNT() の結果でソート

やり方はすごく簡単で、 COUNT() であれば

$foos = $repository->createQueryBuilder('foo')
    ->leftJoin('foo.bars', 'bar')
    ->orderBy('count(bar)', 'desc')
    ->groupBy('foo.id')
    ->getQuery()
    ->getResult()
;

こんな感じでシュッとできます。

参考：php - Doctrine Query builder, count related one to many rows - Stack Overflow

画面に foo.bars の数を表示したい場合は、あとでPHPで数えなくていいように count(bar) の値を addSelect() して結果に含めることも可能です。

$result = $repository->createQueryBuilder('foo')
    ->leftJoin('foo.bars', 'bar')
    ->select('foo as foos')
    ->addSelect('count(bar) as bar_count')
    ->orderBy('bar_count', 'desc')
    ->groupBy('foo.id')
    ->getQuery()
    ->getResult()
;

// [
//   'foos' => [
//     ...
//   ],
//   'bar_count' => xxx,
// ]

ちなみに、このようにメインのエンティティに含まれない値を SELECT に含めたときに、上記のように結果を連想配列にせずに元々のエンティティの配列だけが取得されるようにしたい場合には、以下のように HIDDEN キーワード を使えば解決できます。

  $foos = $repository->createQueryBuilder('foo')
      ->leftJoin('foo.bars', 'bar')
-     ->select('foo as foos')
-     ->addSelect('count(bar) as bar_count')
+     ->addSelect('count(bar) as hidden bar_count')
      ->orderBy('bar_count', 'desc')
      ->groupBy('foo.id')
      ->getQuery()
      ->getResult()
  ;

こうすると、連想配列ではなく Foo エンティティの配列が取得されます。

CONCAT() の結果でソート

CONCAT() の場合もやることは同じです。

$foos = $repository->createQueryBuilder('foo')
    ->addSelect('concat(foo.name1, \' \', foo.name2) as foo_string')
    ->orderBy('foo_string', 'desc')
    ->groupBy('foo.id')
    ->getQuery()
    ->getResult()
;

簡単ですね👍

->addSelect('concat(foo.name1, \' \', foo.name2) as foo_string')

の部分は

->addSelect('concat(foo.name1, " ", foo.name2) as foo_string')

のように " " を使ってしまうとエラーになるので要注意です。

参考：mysql - Doctrine 2 DQL CONCAT fields and constant strings - Stack Overflow

CONCAT() の結果を検索

ついでに CONCAT() の結果に対してソートではなく検索する方法も書いておきます。

これも考え方は同じで、以下のように WHERE 句内で CONCAT() すればよいだけです。

$foos = $repository->createQueryBuilder('foo')
    ->andWhere('concat(foo.name1, \' \', foo.name2) like :query')
    ->setParameter('query', '%'.str_replace('%', '\%', $query).'%')
    ->groupBy('foo.id')
    ->getQuery()
    ->getResult()
;

参考：MySQLでカラムを結合して検索する。あとDoctrineのQueryBuilderでの書き方。 - Tomcky's blog

以下のように SELECT に含めた上で AS でつけた名前に対して LIKE で検索、という書き方はできないので要注意です。

$foos = $repository->createQueryBuilder('foo')
    ->addSelect('concat(foo.name1, \' \', foo.name2) as hidden foo_string')
    ->andWhere('foo_string like :query')
    ->setParameter('query', '%'.str_replace('%', '\%', $query).'%')
    ->groupBy('foo.id')
    ->getQuery()
    ->getResult()
;

まとめ

Doctrineで COUNT() や CONCAT() の結果で ORDER BY したり WHERE で検索したりする方法を解説しました。
もし今までやり方が分からずに諦めてPHP側で処理していたという人がいたらぜひご活用ください😇

Symfony Advent Calendar 2020、明日も僕です！笑 お楽しみに！

GitHubで編集を提案

Discussion