Help us understand the problem. What is going on with this article?

laravel6.0でのsubQueryの書き方(+subQuery vs EagerLoadingについて)

 はじめに

 laravel6.0が出て数日たちましたね。
 今までsubQueryどころかEagerLoadingすらも面倒だからいいやとサボっていた人間なので、
 この機会に少しまとめておきたいと思います。

 laravel6.0でのsubQuery

 今usersとbooksがあるとして

    function ($query) {
            $query->select("name")->from("books")->whereColumn("user_id", "users.id")
                ->limit(1);
    }

 これをaddSelectや、orderByに入れるだけです。

  $users = User::addSelect(["book_name" => function ($query) {
            $query->select("name")->from("books")->whereColumn("user_id", "users.id")
                ->limit(1);
        }])->get();

 foreach ($users as $user) {
            $name = $user->book_name;
        }

 こんな感じです。

 何もしない vs EagerLoading vs subQuery

user_book_er.png
 

 controllerを作ってそこにアクセスして比較していきます。

SampleController.php
  class SampleController extends Controller
    {

    public function index()
    {
        $start = microtime(true);
        $memory = memory_get_usage();

        $this->getBooks();

        $result = [
            "time" => microtime(true) - $start,
            "memory" => (memory_get_peak_usage() - $memory) / (1024 * 1024)
        ];

        dump($result);
    }

    public function getBooks()
    {

        //optionalはuserに対応するbookが必ず存在するとは限らないためつけてます。       

        //通常
        $users = User::all();
        foreach ($users as $user) {
            $name = optional($user->books->first())->name;
        }

        //EagerLoading
        $users = User::with("books")->get();
        foreach ($users as $user) {
            //リレーションメソッドにしてしまうとeagerloadingの効果が失われてしまうので気を付けて
            $name = optional($user->books->first())->name;
        }


        //subQuery
        //addSelectによりusersのすべてと、book_nameが選択されている状態
        $users = User::addSelect(["book_name" => function ($query) {
            $query->select("name")->from("books")->whereColumn("user_id", "users.id")
                ->limit(1);
        }])->get();

        foreach ($users as $user) {
            $name = $user->book_name;

        }
    }
}

 user 1000件 book 1000件の場合

method time memory
通常 2.1359429359436 4.7769012451172
EagerLoading 0.242516040802 4.222671508789
subQuery 0.46214890480042 3.937858581543

 user 3000件 book 3000件の場合

method time memory
通常 8.952919960022 13.542144775391
EagerLoading 0.79955101013184 11.902877807617
subQuery 3.0595591068268 10.085777282715

 user 5000件 book 5000件の場合

method time memory
通常 19.71000289917 28.348297119141
EagerLoading 1.9218430519104 25.727615356445
subQuery 6.256618976593 16.407073974609

 user 10000件 book 10000件の場合
 通常は60秒を超えてtimeoutでした。

method time memory
通常
EagerLoading 3.9436728954315 50.53173828125
subQuery 24.066353082657 31.92147064209

 まとめ

  上の結果だけ見るとEagerLoading一択みたいに見えるんですけど、
  書き方や計測方法が悪いだけなんですかね・・・?
  memory的にはやっぱりsubQueryがいい感じなんだけども・・・
  おそらくどこか間違っていると思うので詳しい方いたら指摘していただければ助かります!

Why do not you register as a user and use Qiita more conveniently?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away