Zenn
🏆

Laravel + MySQLでグループごとにランク付け/指定順位のデータだけ取得する方法(Window関数の代替)

2021/02/04に公開
Laravel
MySQL
tech

「Laravel + MySQLでグループごとにランク付け(順位付け)して指定順位のデータだけ取得する方法」を紹介します。クエリの発行は1回だけです。

いろいろ迷った結果 2(+1)つの方法 を生み出したので、すべて紹介したいと思います！
(供養ってやつです。

こういったパターンに対応できます。

  • MySQLのWindow関数を使いたいけど、バージョン等の理由により使えない
  • SQL発行は1回で、順位付けしたデータの1位だけ取得したい
  • その他、行番号をつけたい、Eloquentでサブクエリを使いたい、などの参考コードとして

この記事を書くにあたって使用したコードをGithubに公開しておきました。
色々試してみたい方は使ってみてください。記事の最後の関連リンクに置いています。

やりたいこと

例として、「目的地別に、最後のフライトを表示する」 ということをやります。
飛行機が到着すると、目的地や到着時間を記録したフライトが記録されるイメージです。
実際にテーブルとデータを見てみましょう。

使うのは以下の2つです。

使用するテーブルとデータ

flights

目的地ID、便名、到着時間を管理する。

+----------------+-----------------+------+-----+---------+----------------+
| Field          | Type            | Null | Key | Default | Extra          |
+----------------+-----------------+------+-----+---------+----------------+
| id             | bigint unsigned | NO   | PRI | NULL    | auto_increment |
| destination_id | int unsigned    | NO   |     | NULL    |                |
| name           | varchar(255)    | NO   |     | NULL    |                |
| arrived_at     | datetime        | NO   |     | NULL    |                |
| created_at     | timestamp       | YES  |     | NULL    |                |
| updated_at     | timestamp       | YES  |     | NULL    |                |
+----------------+-----------------+------+-----+---------+----------------+

+----+----------------+-------+---------------------+
| id | destination_id | name  | arrived_at          |
+----+----------------+-------+---------------------+
|  1 |              1 | ZN001 | 2021-02-01 08:00:00 |
|  2 |              1 | QT001 | 2021-02-01 10:00:00 |
|  3 |              1 | ZN002 | 2021-02-01 12:00:00 |
|  4 |              2 | ZN003 | 2021-02-01 14:00:00 |
|  5 |              2 | QT002 | 2021-02-01 16:00:00 |
+----+----------------+-------+---------------------+

destinations

目的地名を管理する。

+------------+-----------------+------+-----+---------+----------------+
| Field      | Type            | Null | Key | Default | Extra          |
+------------+-----------------+------+-----+---------+----------------+
| id         | bigint unsigned | NO   | PRI | NULL    | auto_increment |
| name       | varchar(255)    | NO   |     | NULL    |                |
| created_at | timestamp       | YES  |     | NULL    |                |
| updated_at | timestamp       | YES  |     | NULL    |                |
+------------+-----------------+------+-----+---------+----------------+

+----+-------------+
| id | name        |
+----+-------------+
|  1 | バーチャル東京 |
|  2 | バーチャル大阪 |
+----+-------------+

方法１　上級サブクエリを使う

「上級サブクエリ」というものを使います。
【参照】上級サブクエリ

orderByDescで降順に並べ替えたものの中から、limitで最初の1件だけ取得する方法です。

return Destination::addSelect([
    'last_flight' =>
        Flight::select('name')
            ->whereColumn('destination_id', 'destinations.id')
            ->orderByDesc('arrived_at')
            ->limit(1)
        ])->get();

addSelectのカラムの一つにSQLをまるごと書いたようなクエリですね。

結果はこのようになります。

データと見比べてみると、確かに目的地別の最後のフライト名を取得できています。

発行されたSQLを見ると、項目が1つしか取得できない理由に納得。
↓↓↓

select
`destinations`.*
, (select
     `name`
   from `flights`
   where
     `destination_id` = `destinations`.`id`
   order by
     `arrived_at` desc limit 1
  ) as `last_flight` 
from `destinations`

selectの中にサブクエリ(selectの中のselect)で書かれていたんですね
(Laravelのドキュメントに「サブクエリ」と書かれているので当然っちゃ当然ですけど！)

サブクエリは単一行、単一カラムで返すのが基本なので、取得できるのは1項目だけなんだな、と理解しました。
【参照】サブクエリー構文

方法２　ユーザー定義変数を使う

私が方法１があることを知らずに実装してしまった方法がこちらです。
MySQLの「ユーザー定義変数」というものを使います。
【参照】ユーザー定義変数

ちょっと複雑ですが、こちらは1位以外にも取得できる方法です。

// ユーザー定義変数をセット
DB::statement('SET @row_num = 0;');
// destination_idごとに順位付け
$flights = Flight::query()
    ->select(
        'flights.*',
        DB::raw('if(@_last_id != flights.destination_id
	  , @row_num := 1
	    , @row_num := @row_num + 1) as row_num'),
        DB::raw('@_last_id := flights.destination_id as last_id')
    )
    ->orderBy('flights.destination_id')
    ->orderByDesc('flights.arrived_at');

return Destination::query()
    ->select([
        'destinations.*',
        'flights.name as flights_name'
    ])
    ->joinSub($flights, 'flights', function ($join) {
        $join->on('destinations.id', '=', 'flights.destination_id')
             ->where('flights.row_num', 1);
        })
    ->get();

かなり遠回しっぽいやり方ですが、こっちの方が柔軟性はあります(DBがMySQLじゃないとけない制限はあります)。
結構複雑になってしまったので、解説します。

クエリ１を解説

// ユーザー定義変数を定義
DB::statement('SET @row_num = 0;');  ←①
// destination_idごとに順位付け
$flights = Flight::query()
    ->select(
        'flights.*',
        DB::raw('if(@_last_id != flights.destination_id
	  , @row_num := 1
	    , @row_num := @row_num + 1) as row_num'),  ←③
        DB::raw('@_last_id := flights.destination_id as last_id')  ←④
    )
    ->orderBy('flights.destination_id')  ←②
    ->orderByDesc('flights.arrived_at');

①まずはユーザー定義変数 @row_num を定義して初期化します。

②Flightsを、destination_idの昇順・arrived_atの降順に並べ替えます。

③selectでdestination_idごとに順位 @row_num を振っていきます
1行毎にIFが評価されて、
　同じdestination_idであれば、@row_num をインクリメント
　違うdestination_idであれば、@row_num を初期化
↑これをMySQLのIFで実現しています。
IFは、引数1が真なら引数2、偽なら引数3を返します。

④1行前の destination_id を @_last_id に保持します。初回は空です

クエリ２を解説

return Destination::query()
    ->select([
        'destinations.*',
        'flights.name as flights_name'
    ])
    ->joinSub($flights, 'flights', function ($join) {
        $join->on('destinations.id', '=', 'flights.destination_id')  →⑤
             ->where('flights.row_num', 1);  →⑥
        })
    ->get();

⑤１つめのクエリを、Destinationsと結合します。

⑥順位付けした中の、1位のフライトのみ取得します。

こんな感じです。

やり方は違いますが、方法１と同じ結果が得られます。

ちなみに方法２の方は、selectに追加すれば到着時間も一緒に取得することができます。

番外編　Window関数を使う

Window関数を使うとどういうSQLになるのかも一応見ておきましょう。
クエリビルダでは対応できないので、Laravelでは生のSQLを書くことになります。

return DB::select("
    SELECT
      d.id as id
    , d.name as name
    , f.name as last_flight
    FROM
      destinations d
    JOIN
        (
      SELECT *
      , ROW_NUMBER() OVER (PARTITION BY destination_id
                            ORDER BY arrived_at DESC) AS row_num
      FROM flights
      ) f
    ON
        d.id = f.destination_id
    AND f.row_num = 1
");

以上、Laravel + MySQLでグループごとにランク付け/指定順位のデータだけ取得する方法を紹介しました！

関連リンク

https://github.com/paya02/mysql-query

Discussion