何をするのか
1対多 の関係において,子テーブルの最大/最小のレコードを基準に,親テーブルをソートする方法を紹介します。
具体例
1つの記事に複数のコメントが紐づくケースを考えます。Article
と Comment
が 1対多 の関係で結びついています。
ここで「最近コメントされた記事」を取得するにはどうすればよいでしょうか。
その際に,コメントの日時と内容も同時に取得するとしましょう。さらに,48時間以内にコメントされた記事だけに限定します。
大まかな流れ
(1) 同一の article_id
を持つコメントから,最新のレコードのみを抽出する
(この段階で,comment は article と 1対1 で対応する)
(2) 抽出したレコードと記事テーブルをJOINしてデータを並び替える
実装する
Article モデルに scope として定義します。
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 モデルからアプローチしても実現できるはずです。(そっちの方が楽かも)
改善案などあればコメントしていただけたら嬉しいです。