Laravel忘備録-重複を削除し最新のレコードを取得-

重複を削除し、最新のレコードを取得する機会があったため忘備録として記録に残そうと思います。

想定するデータ

purchase_historiesテーブル
購入履歴のようなテーブルを想定しています。

purchase_histories

+---------------+---------------------+------+-----+---------+----------------+
| Field         | Type                | Null | Key | Default | Extra          |
+---------------+---------------------+------+-----+---------+----------------+
| id            | bigint(20) unsigned | NO   | PK  | NULL    | auto_increment |
| user_id       | bigint(20) unsigned | NO   | FK  | NULL    |                |
| product_id    | bigint(20) unsigned | NO   | FK  | NULL    |                |
| created_at    | timestamp           | YES  |     | NULL    |                |
| updated_at    | timestamp           | YES  |     | NULL    |                |
+---------------+---------------------+------+-----+---------+----------------+

ユーザーが最も最近購入した商品を取得したい

手順

1. purchase_historiesテーブルからuser_idの重複を削除し、でcreated_atが最も新しいレコードを取得する
2. 1.で取得したuser_id、created_atと等しいレコードを同じくpurchase_historiesテーブルを使いJOINする
3. 2.でJOINした結果を受け取る

上記の手順でユーザー(user_id)の重複を削除し、直近購入した商品(product_id)を取得することができます。

code

// user_idの重複を削除し、created_atが新しいレコードを取得
$users = DB::table('purchase_histories')
            ->select('user_id', DB::raw('MAX(created_at) as last_created_at'))
            ->groupBy('user_id');

// 上記で取得したuser_idとcreated_atが一致するレコードを同じテーブルにJOINする
$latestPurchases = DB::table('purchase_histories')
            ->joinSub($users, 'users', function ($join) {
                $join
                    ->on('purchase_histories.user_id', '=', 'users.user_id')
                    ->on('purchase_histories.created_at', '=', 'users.last_created_at');
            })->get();

Where句でレコードを絞りたい

where句にuser_idを指定した場合に以下のエラーが返ってきました。
where句が曖昧です。と怒られます。

SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'user_id' in where clause is ambiguous

テーブル名を指定しないとどっちのuser_idを使えば良いかわからない。
と怒られるようです。

修正前

$latestPurchases = DB::table('purchase_histories')
            ->joinSub($users, 'users', function ($join) {
                $join
                    ->on('purchase_histories.user_id', '=', 'users.user_id')
                    ->on('purchase_histories.created_at', '=', 'users.last_created_at');
            })
            ->where('user_id', '=', '1234') // <-ここで怒られます
            ->get();

修正後

$latestPurchases = DB::table('purchase_histories')
            ->joinSub($users, 'users', function ($join) {
                $join
                    ->on('purchase_histories.user_id', '=', 'users.user_id')
                    ->on('purchase_histories.created_at', '=', 'users.last_created_at');
            })
            ->where('purchase_histories.user_id', '=', '1234') // <-テーブル名を明示します
            ->get();

参考資料

→ サブクエリのJOINを参照

告知

最後にお知らせとなりますが、イーディーエーでは一緒に働くエンジニアを
募集しております。詳しくは採用情報ページをご確認ください。

みなさまからのご応募をお待ちしております。