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);
ありがとうございました。