with查询

关联查询

 DB::enableQueryLog();
        $filter = [];
        $filter['course_id'] = $course_id;
//        $filter['with']['user'] = [];
        CourseUser::items($filter);
        $query =DB::getQueryLog();
        return self::success($query);

打印出的sql执行如下

 {
            "query": "select count(*) as aggregate from `wm_course_user` where (`wm_course_user`.`is_delete` = ?)",
            "bindings": [
                0
            ],
            "time": 0.28
        },
        {
            "query": "select `wm_course_user`.`id`, `wm_course_user`.`user_id`, `wm_course_user`.`course_id`, `wm_course_user`.`created_at` from `wm_course_user` where (`wm_course_user`.`is_delete` = ?) order by `wm_course_user`.`id` desc limit 20 offset 0",
            "bindings": [
                0
            ],
            "time": 0.28
        }

发现我不取用关联的数据，不会执行sql查询，这就是懒加载。现在我去使用关联的数据，看下查询

DB::enableQueryLog();
        $filter = [];
        $filter['course_id'] = $course_id;

//        $filter['with']['user'] = [];
        $items = CourseUser::items($filter);
        foreach($items as $value){
            var_dump($value->user->name);
        }
        $query =DB::getQueryLog();
        return self::success($query);

打印执行的sql如下

{
            "query": "select count(*) as aggregate from `wm_course_user` where (`wm_course_user`.`is_delete` = ?)",
            "bindings": [
                0
            ],
            "time": 0.25
        },
        {
            "query": "select `wm_course_user`.`id`, `wm_course_user`.`user_id`, `wm_course_user`.`course_id`, `wm_course_user`.`created_at` from `wm_course_user` where (`wm_course_user`.`is_delete` = ?) order by `wm_course_user`.`id` desc limit 20 offset 0",
            "bindings": [
                0
            ],
            "time": 0.27
        },
        {
            "query": "select * from `wm_user` where `wm_user`.`id` = ? limit 1",
            "bindings": [
                1643
            ],
            "time": 0.44
        },
        {
            "query": "select * from `wm_user` where `wm_user`.`id` = ? limit 1",
            "bindings": [
                2
            ],
            "time": 0.37
        }

看到每个关联都要查询一次数据库，这就是所谓的N+1的查询问题。

DB::enableQueryLog();
        $filter = [];
        $filter['course_id'] = $course_id;

//        $filter['with']['user'] = [];
        $items = CourseUser::items($filter);
        foreach($items as $value){
            if($value->user_id == 2){
                var_dump($value->user->name);
            }
        }
        $query =DB::getQueryLog();
        return self::success($query);

执行语句

{
            "query": "select count(*) as aggregate from `wm_course_user` where (`wm_course_user`.`is_delete` = ?)",
            "bindings": [
                0
            ],
            "time": 0.28
        },
        {
            "query": "select `wm_course_user`.`id`, `wm_course_user`.`user_id`, `wm_course_user`.`course_id`, `wm_course_user`.`created_at` from `wm_course_user` where (`wm_course_user`.`is_delete` = ?) order by `wm_course_user`.`id` desc limit 20 offset 0",
            "bindings": [
                0
            ],
            "time": 0.25
        },
        {
            "query": "select * from `wm_user` where `wm_user`.`id` = ? limit 1",
            "bindings": [
                2
            ],
            "time": 0.47
        }

如何避免N+1问题呢？使用with查询进行预加载

DB::enableQueryLog();
$filter = [];
$filter['course_id'] = $course_id;
$filter['with']['user'] = [];
CourseUser::items($filter);
$query =DB::getQueryLog();
return self::success($query);

打印出执行的sql如下

 {
            "query": "select count(*) as aggregate from `wm_course_user` where (`wm_course_user`.`is_delete` = ?)",
            "bindings": [
                0
            ],
            "time": 0.28
        },
        {
            "query": "select `wm_course_user`.`id`, `wm_course_user`.`user_id`, `wm_course_user`.`course_id`, `wm_course_user`.`created_at` from `wm_course_user` where (`wm_course_user`.`is_delete` = ?) order by `wm_course_user`.`id` desc limit 20 offset 0",
            "bindings": [
                0
            ],
            "time": 0.31
        },
        {
            "query": "select * from `wm_user` where `wm_user`.`id` in (2, 1643)",
            "bindings": [],
            "time": 0.43
        }

一共查询了3次。

当以属性方式访问 Eloquent 关联时，关联数据「懒加载」。这着直到第一次访问属性时关联数据才会被真实加载。不过 Eloquent 能在查询父模型时「预先载入」子关联。预加载可以缓解 N + 1 查询问题。

什么时候使用懒加载，什么时候使用预加载呢？列表数据需要展示的内容使用预加载、在详情里面才需要展示的内容使用懒加载。