何をするのか

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

具体例

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

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

大まかな流れ

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

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

実装する

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

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;
}

使用例

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

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 モデルからアプローチしても実現できるはずです。（そっちの方が楽かも）

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