【Laravel】1対多のリレーションから各々の最新の1件を取り出す

Laravel で 1 対多のリレーションから最新の 1 件を取り出して、1 対 1 の組み合わせを作るのが大変つらかったのでメモ

ユーザが新しいデバイスでログインするたびに記録を行う devices テーブルが user has many devices(1 対多) の形で存在する。

devices にはログインした IP アドレスとユーザエージェント・作成日時・更新日時があり、更新日時で最新の 1 件を user 側から取りたい。さらに何かしらの条件を付け加えて結果を絞り込みたい。

通常 user が所持しているデバイスの一覧を取得するには以下のようにする。

public function devices()
{
  return $this->hasMany(Device::class)
}

最新の 1 台を取得したいのであればhasOneとorderByを使うことができる。

Laravel で DB 上は hasMany だけど 1 件だけ取りたいときは hasOne を使うといい - Qiita

public function latestLoginDevice()
{
  return $this->hasOne(Device::class)
    ->orderBy('updated_at', 'desc');
}

これで 1 人のユーザからは最新のデバイスが取得できるようになったが、複数人のユーザから同じように取得することはできないらしい。

通常リレーション先で条件マッチを行うにはwhereHasを使うが、whereHasでlatestLoginDeviceを指定しても、devicesで呼び出すのと変わらない SQL が発行される。

ログインが最新のデバイスかつ user_agent が Chrome であったものを集計したいが、以下のコードでは実現できない。

$users = User::whereHas('latestLoginDevice', function ($query) {
  return $query->where('user_agent', 'like', '%Chrome%');
});

dd($users->toSql());
// "select * from `users` where exists (select * from `devices` where `users`.`id` = `devices`.`user_id` and `user_agent` like ?)"

$users = User::whereHas('devices', function ($query) {
  return $query->where('user_agent', 'like', '%Chrome%');
});

dd($users->toSql());
// "select * from `users` where exists (select * from `devices` where `users`.`id` = `devices`.`user_id` and `user_agent` like ?)"

latestLoginDeviceに記述したはずのオーダーが実行されておらず、ただ結合して検索をしているだけになっている。これでは 1 人あたり複数のデバイスの結果が返ってきてしまう。

Laravel で実現するには面倒な記述が必要になる。

public function scopeLatestLoginWithChromeDevice($query, $user_agent)
{
  $sub_query = Device::select(DB::raw('user_id, max(updated_at)'))
    ->groupBy('user_id');
  return $query->whereHas('devices', function ($query) use ($sub_query, $user_agent) {
    return $query->joinSub($sub_query->toSql(), 'dvc', function ($join) {
      $join->on('devices.user_id', '=', 'dvc.user_id')
        ->on('devices.updated_at', '=', 'dvc.updated_at');
    })->where('user_agent', 'like', "%{$user_agent}%");
  });
}

1. サブクエリを使って同じuser_idをグループ化しつつ、updated_atで大きいものを採用する。

2. グループ化では、2 つのカラムしかとってこられない(?)ので、devicesと結合し、user_agentが含まれるようにする。

$user_agent = 'Chrome';
$users = User::latestLoginWithChromeDevice($user_agent);

ウィンドウ関数を使えばdevices同士の結果を組み合わせる必要がなくなるのだが、MySQL では 8.0 からようやく使えるようで現環境では使えなかった。

サブクエリは効率的ではなく、あまり使いたくないのだがこうする他なかったので暫定的に採用。

p { margin-bottom: 1em; } blockquote p { margin-bottom: initial; } #content ul { padding-left: 40px; } .token.operator { background-color: initial; }