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