search
LoginSignup
1
Help us understand the problem. What are the problem?

posted at

updated at

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

環境

Laravel v9.5.1 (PHP v8.1.3)

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

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

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

書き方①

$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
  ]
]

Register as a new user and use Qiita more conveniently

  1. You can follow users and tags
  2. you can stock useful information
  3. You can make editorial suggestions for articles
What you can do with signing up
1
Help us understand the problem. What are the problem?