1.はじめに

タイトルの通りですが何を言ってるのかわからない。
しかし、絶対にこれをやりたい時が来るはずです。
自分はいつも詰まる。
例えば以下の機能があったとする。

「顧客がいて、顧客がレストランを予約する。」
顧客テーブル(customers)
予約テーブル(reserves)

一応、modelでリレーション張るとしたら以下の感じ

Customer.php

//一人の顧客はたくさんの予約を持つ。
public function reserves()
  {
    return $this->hasMany(Reserve::class);
  }

Reserve.php

//予約は一人の顧客に属する(一つの予約が複数の顧客idを持つわけがない)
public function customer()
  {
    return $this->belongsTo(Customer::class);
  }

こちらreserves_table

id	customer_id	restaurant_id	updated_at	created_at
1	1	3	2022-11-05 16:34:29.000 +0900	2022-11-05 16:34:29.000 +0900
2	3	1	2021-05-09 11:01:14.000 +0900	2021-05-09 11:01:14.000 +0900
3	1	5	2022-05-21 16:51:14.000 +0900	2022-05-21 16:51:14.000 +0900
4	2	4	2021-9-22 09:41:09.000 +0900	2021-9-22 09:41:09.000 +0900

ほんでcustomers_tabel

id	customer_name	age	updated_at	created_at
1	chaki	34	2021-01-06 16:34:29.000 +0900	2021-01-06 16:34:29.000 +0900
2	hoge	19	2021-02-09 07:23:11.000 +0900	2021-02-09 07:23:11.000 +0900
3	ketu	22	2021-9-02 09:22:39.000 +0900	2021-9-02 09:22:39.000 +0900

どんな時にタイトルのような訳のわからない事をしたくなるかと言うと。。。
次の様な要件。
・顧客ごとの予約件数を取得したい
・それを予約の新しい順で並べ替えたい。
意外とシンプル。
以下の様に取得したい感じ。

customer_id	customer_name	件数	updated_at	created_at
1	chaki	2	2022-11-05 16:34:29.000 +0900	2022-11-05 16:34:29.000 +0900
2	hoge	1	2021-9-22 09:41:09.000 +0900	2021-9-22 09:41:09.000 +0900
3	ketu	1	2021-05-09 11:01:14.000 +0900	2021-05-09 11:01:14.000 +0900

どうすれば良いかというとGroupByしてその中の最新レコード取ってきてさらにjoinして最後にOrderBy!!!!
タイトルの通りです。
サブクエリ使います。

query.php

$subSql =  CustomerTransfer::select(DB::raw('customer_id, MAX(created_at) as latest(名前自由)'))
          ->where('deleted_at', null)
          ->groupBy('customer_id')
          ->toSql();
        return $query->select(DB::raw('*'))
          ->joinSub($subSql, 'reserves', function ($join) {
            $join->on('customers.id', '=', 'reserves.customer_id');
          })->orderBy('latest', 'desc');//昇順ならdescをascに変更

素のSQLで書けば楽かもですがLaraveでクエリビルダから脱却しようとすると色々めんどくさいです。
ページネーションの「->pagenate()」とか子テーブル取ってくる時の「->with()」とかくっつけられなくなるので。
素のSQL書かなきゃいけない時はテーブル設計を見直したほうが早いかもです。
ただ、コードにテーブル設計合わせるよりテーブル設計ありきのコードだとも思うのでなんとも言えませんが。
影響範囲や保守性とかの兼ね合いですかね。

ちなみでガチで生のSQL使いたければ以下の様な感じ。
SQLインジェクションとか意識しないといけないので使うのめんどくさいと思いますが。

query.php

$sql = <<< SQL
    SELECT
        c.name,
    FROM
       customers c 
    INNER JOIN
        reserves r 
    ON
        c.id = r.customer_id
    WHERE
        c.deleted_at = null
SQL;

$names = DB::select($sql);

ありがとうございました。