Laravel with() vs join()

資料情境:

程式碼:

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;

use App\Http\Controllers\Controller;

use App\Services\UserService;

use App\Models\Comment;

use App\Models\Article;

use DB;

use Exception;

class ArticleController extends Controller

{

    public function query(){

        /*

        for($i = 1 ; $i <= 20000 ; $i++){

            $comment = new Comment;

            $comment->article_id = 2;

            $comment->content = substr(md5(mt_rand()), 0, 50);

            $comment->save();

        }

        */

        try{

            DB::enableQueryLog();

            //---------------------------------------------------------------------------------

            $start = microtime(true);

            $query = Article::join('comment', 'comment.article_id', '=', 'article.id')

            ->select(

                'article.id',

                'article.title AS article_title',

                'comment.content AS comment_content'

            );

            $join_result = $query->get();

            $join_time = microtime(true) - $start;

            //---------------------------------------------------------------------------------

            $start = microtime(true);

            $query = Article::with([

                'comments' => function ($query) {

                    $query->select([

                        'article_id',

                        'content'

                    ]);

                }

            ]);

            $with_result = $query->get();

            $with_time = microtime(true) - $start;

            //---------------------------------------------------------------------------------

            $SQL = DB::getQueryLog();

            return response()->json([

                'status' => 'success',

                'SQL' => $SQL,

                'join_time' => $join_time,

                'join_result' => $join_result,

                'with_time' => $with_time,

                'with_result' => $with_result,

            ]);

        }

        catch (Exception $e) {

            return response()->json([

                'status' => 'fail',

                'message' => $e->getMessage()

            ], 500);

        }

    }

}

實驗結果:

• 從結果可以看出，with這種寫法，共下了兩次sql，分別把文章跟留言都撈出來，再把資料mapping起來，with_result如下:

• join寫法就不多解釋了，join_result如下:

• join執行時間6.6s，with執行時間8.4s，單從執行時間上來看，join快一些。
• 就資料面來看，with把資料整理得比較乾淨，大致上分為兩篇文章，下面的comments key就可以直接拿到該篇文章的所有留言，如果這個query api的目的是要抓出所有文章跟對應的留言的話，用with這種寫法比較簡潔，而且前端拿到資料後也幾乎不需要再整理過，可以說是好處多多。但如果是join寫法，可能還要group by 文章id，才能整理成with這種資料結構。

結論:

• join與with寫法，回傳的資料結構不同，得視使用情境來選擇適合的寫法。

1. https://learnku.com/laravel/t/30387
2. https://laracasts.com/discuss/channels/laravel/laravel-join-vs-with-in-performance
3. https://learnku.com/laravel/t/16565/lets-talk-about-the-comparison-between-laravel-with-and-join-welcome-to-explain
4. https://segmentfault.com/q/1010000006114982