[Laravel] 子テーブルの最大/最小のレコードを基準に親テーブルを並び替える


# 何をするのか

1対多 の関係において，子テーブルの最大/最小のレコードを基準に，親テーブルをソートする方法を紹介します。

## 具体例

1つの記事に複数のコメントが紐づくケースを考えます。`Article` と `Comment` が 1対多 の関係で結びついています。

ここで「最近コメントされた記事」を取得するにはどうすればよいでしょうか。
その際に，コメントの日時と内容も同時に取得するとしましょう。さらに，48時間以内にコメントされた記事だけに限定します。

## 大まかな流れ

(1) 同一の `article_id` を持つコメントから，最新のレコードのみを抽出する
    　　(この段階で，comment は article と 1対1 で対応する)

(2) 抽出したレコードと記事テーブルをJOINしてデータを並び替える

# 実装する

Article モデルに scope として定義します。

```php:Article.php
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Database\Query\JoinClause;
use Illuminate\Support\Facades\DB;
use Carbon\Carbon;

/**
 * @param  \Illuminate\Database\Eloquent\Builder $query
 * @param  int $h
 * @return \Illuminate\Database\Eloquent\Builder
 */
public function scopeLatestComment(Builder $query, int $h = 24): Builder
{
    // $h 時間前の日時
    $point = Carbon::now()->subHour($h)->format('Y-m-d H:i:s');

    // 同一の article_id を持つコメントから，最新のレコードのみを抽出するサブクエリ
    $subSQL = DB::table('comments as x')
        ->select('x.*')
        ->leftJoin('comments as y', function (JoinClause $join) {
            $join->on('x.article_id', '=', 'y.article_id')
                ->on('x.id', '<', 'y.id')
                ->whereNull('y.deleted_at');
        })
        ->whereNull('x.deleted_at')
        ->whereNull('y.id')
        ->where('x.created_at', '>', $point);

    // サブクエリの結果を軸として，articles を JOIN する
    $query->rightJoinSub($subSQL, 'latest_comments', function (JoinClause $join) {
        $join->on('articles.id', '=', 'latest_comments.article_id');
    })
        ->selectRaw('articles.*, latest_comments.msg, latest_comments.created_at as commented_at')
        ->orderBy('commented_at', 'desc')
        ->orderBy('id', 'desc');

    return $query;
}
```

## 使用例

```php
// 48時間以内にコメントされた記事について，最新のコメント日時でソートして取得
$articles = Article::LatestComment(48)->limit(10)->get(); 
```

## SQL を確認する

```sql
SELECT articles.*, latest_comments.msg, latest_comments.created_at AS commented_at 
FROM articles

RIGHT JOIN (
    SELECT x.* 
    FROM comments AS x 
    LEFT JOIN comments AS y 
        ON x.article_id = y.article_id 
            AND x.id < y.id 
            AND y.deleted_at IS NULL 
    WHERE x.deleted_at IS NULL 
        AND y.id IS NULL 
        AND x.created_at > 2021-08-10 00:03:17
) AS latest_comments 

ON articles.id = latest_comments.article_id 
WHERE articles.deleted_at IS NULL 
ORDER BY commented_at DESC, id DESC 
LIMIT 10
```

# 解説

サブクエリでは「同一の `article_id` の中で最新 ( id が最大) のレコード」を取得しています。

commentsテーブル ( x ) を軸に， commentsテーブル ( y ) を JOIN しています。
JOIN の条件として `x.id < y.id ` を指定します。その上で `WHERE y.id IS NULL ` とします。

なぜこれでグループ内の最新のレコードを抽出できるのでしょうか。

例えば，ある記事に紐づくコメントが 5件 あるとします。
コメントの id がそれぞれ `1` `3` `5` `6` `10` だったとき，最新のコメントは `10` です。

`xテーブル` のレコードが `1` `3` `5` `6` の場合は，`x.id < y.id ` の条件より，少なくとも `yテーブル` の `10` はJOINの条件に一致します。

`xテーブル` のレコードが `10` の場合のみ，条件に一致する `yテーブル` のレコードが存在しないので `NULL` が JOIN されます。

つまり，x を軸に `x.id < y.id ` の条件で JOIN した上で，`WHERE y.id IS NULL ` で絞り込むことで，グループ内の最新のレコードを取得できます。

この段階で，comments (サブクエリ) と articles が 1対1 で対応します。

あとはサブクエリの結果を軸にJOINして，コメントの投稿日時で並び替えるだけです。

# 最後に

今回は Aericle モデルに scope を定義しましたが，Comment モデルからアプローチしても実現できるはずです。（そっちの方が楽かも）

改善案などあればコメントしていただけたら嬉しいです。