【Laravel】クエリビルダ/whereIn/whereHasを使った情報取得

環境

Laravel v9.5.1 (PHP v8.1.3)

リレーション先の条件での絞り込み

organization -> user -> post
(->は1対多の関係)

同じorganizationに所属するuserのpost一覧を最新順に表示したい。

書き方①

$orgUsersIds = User::where('organization_id', $user->organization_id)
                        ->with('organization')
                        ->pluck('id')
                        ->toArray();
Post::whereIn('user_id', $orgUsersIds)
       ->latest();
       ->paginate(25);

 array:3 [
  0 => array:3 [
    "query" => "select `id` from `users` where `organization_id` = ?"
    "bindings" => array:1 [
      0 => 1
    ]
    "time" => 1.44
  ]
  1 => array:3 [
    "query" => "select count(*) as aggregate from `posts` where `user_id` in (?, ?)"
    "bindings" => array:2 [
      0 => 1
      1 => 2
    ]
    "time" => 1.31
  ]
  2 => array:3 [
    "query" => "select * from `posts` where `user_id` in (?, ?) order by `created_at` desc limit 25 offset 0"
    "bindings" => array:2 [
      0 => 1
      1 => 2
    ]
    "time" => 1.43
  ]
]

書き方②

DBクエリビルダ的に書くと

Post::select('posts.*')
      ->join('users', 'posts.user_id', 'users.id')
      ->where('users.organization_id', $user->organization_id)
      ->latest('posts.created_at')
      ->paginate(25);

array:2 [
  0 => array:3 [
    "query" => "select count(*) as aggregate from `posts` inner join `users` on `posts`.`user_id` = `users`.`id` where `users`.`organization_id` = ?"
    "bindings" => array:1 [
      0 => 1
    ]
    "time" => 2.11
  ]
  1 => array:3 [
    "query" => "select `posts`.* from `posts` inner join `users` on `posts`.`user_id` = `users`.`id` where `users`.`organization_id` = ? order by `posts`.`created_at` desc limit 25 offset 0"
    "bindings" => array:1 [
      0 => 1
    ]
    "time" => 2.55
  ]
]

書き方③

whereHasを使うと遅くなるらしいがテストの実行時間は他と変わらなかった。

Post::whereHas('user', function (Builder $query) use ($user) {
      $query->where('organization_id', $user->organization_id);
})->latest()->paginate(25);

 array:2 [
  0 => array:3 [
    "query" => "select count(*) as aggregate from `posts` where exists (select * from `users` where `posts`.`user_id` = `users`.`id` and `organization_id` = ?)"
    "bindings" => array:1 [
      0 => 1
    ]
    "time" => 1.41
  ]
  1 => array:3 [
    "query" => "select * from `posts` where exists (select * from `users` where `posts`.`user_id` = `users`.`id` and `organization_id` = ?) order by `created_at` desc limit 25 offset 0"
    "bindings" => array:1 [
      0 => 1
    ]
    "time" => 1.3
  ]
]

書き方④

書き方③のwhereHasは遅いから代わりにwhereInを使うといいという記事をいくつも見た。

Post::whereIn('user_id', function ($query) use ($user) {
      $query->from('users')
            ->select('users.id')
            ->where('users.organization_id', $user->organization_id);
})->latest()->paginate(25);

 array:2 [
  0 => array:3 [
    "query" => "select count(*) as aggregate from `posts` where `user_id` in (select `users`.`id` from `users` where `users`.`organization_id` = ?)"
    "bindings" => array:1 [
      0 => 1
    ]
    "time" => 1.67
  ]
  1 => array:3 [
    "query" => "select * from `posts` where `user_id` in (select `users`.`id` from `users` where `users`.`organization_id` = ?) order by `created_at` desc limit 25 offset 0"
    "bindings" => array:1 [
      0 => 1
    ]
    "time" => 1.33
  ]
]