求大佬看看关联表的查询，可以怎么优化优化？

Question

public function index(Request $request)
    {
        $res = Article::orderBy('id', 'desc')->where('is_delete', 0)->where(function ($query) use ($request) {
            if (!empty($request->time_start) && !empty($request->time_end)) {
                $query->where('created_at', '>=', $request->time_start)
                    ->where('created_at', '<=', $request->time_end);
            }
            if (!empty($request->keyword)) {
                $query->where('title', 'like', "%{$request->keyword}%")->orWhere('content', 'like', "%{$request->keyword}%");
            }
            if (!empty($request->tag)) {
                $query->where('tag_id', 'like', "%{$request->tag}%");
            }
            if (!empty($request->type)) {
                $query->where('articleType_id', $request->type);
            }
            if (!empty($request->user)) {
                $query->where('user_id', $request->user);
            }
        })->simplePaginate(10);
//        $user_id = session('userInfo')['id'];
        foreach ($res as $k => $v) {
            $v->user = User::find($v->user_id)->first();
            $v->user->headpic = asset($v->user->headpic);
            $v->typename = ArticleType::find($v->articleType_id)->typename;
            $ArticleUp = ArticleUp::select('type')->where('article_id', $v->id)->where('user_id', $v->user_id)->first();
            if ($ArticleUp) {
                $v->is_up = $ArticleUp->type;
            } else {
                $v->is_up = '';
            }
            $save = ArticleSave::where('article_id', $v->id)->where('user_id', $v->user_id)->first();
            if ($save) {
                $v->is_save = true;
            } else {
                $v->is_save = false;
            }
        }
        return DataReturn::ReturnJson('success', $res);
    }

求大佬给优化优化写法，总是感觉这么写不太好，
欢迎指正

Answer 1

看了下你写的，变量名称乱七八糟，而且循环获取单个模型变量？推荐看下laravek 预查询，这里你的这种写法我给你稍微优化你看看，修改了部分参数，不要全部复制，明白主题意思即可

        $articles = Article::query()->orderBy('id', 'desc')->where('is_delete', 0)->where(function ($query) use ($request) {
            $time_start = $request->get('time_start');
            $time_end = $request->get('time_end');
            $keyword = $request->get('keyword');
            $tag = $request->get('tag');
            $type = $request->get('type');
            $user = $request->get('user');

            $time_start && $time_end && $query->where('created_at', '>=', $time_start)->where('created_at', '<=', $time_end);
            $keyword && $query->where('title', 'like', "%$keyword%")->orWhere('content', 'like', "%$keyword%");
            $tag && $query->where('tag_id', 'like', "%$tag%");
            $type && $query->where('articleType_id', $type);
            $user && $query->where('user_id', $user);
        })->forPage($request->get('page'), 10)->get()->toArray(); # 这里改成了 forPage ， 你自由调整一下改回来即可

        # 获取唯一参数便于whereIn
        $user_ids = array_column($articles, 'user_id');
        $articleType_ids = array_column($articles, 'articleType_id');
        
        # whereIn批量获取检少查询次数
        # keyBy 用于快速通过索引拿到数据信息
        $users = User::query()->whereIn('id', $user_ids)->get(['headpic', 'id'])->keyBy('id')->toArray();
        $articleTypes = ArticleType::query()->whereIn('id', $articleType_ids)->get(['typename', 'id'])->keyBy('id')->toArray();

        foreach ($articles as $index => $article) {
            # 其他语法格式 $article['user_id'] ?? ''；
            $user_id = Arr::get($article, 'user_id', '');
            $articleType_id = Arr::get($article, 'articleType_id', '');

            $user = Arr::get($users, $user_id, []);
            $user['headpic'] = Arr::get($user, 'headpic', '');
            $articleType = Arr::get($articleTypes, $articleType_id, []);
            $data = [
                'user' => $user,
                'typename' => $articleType['typename'] ?? ''
            ];

            # ！！！下面的就不写了 同理 ｜ 自己差不多写就行！！！！
//            $v->typename = ArticleType::find($v->articleType_id)->typename;
//            $ArticleUp = ArticleUp::select('type')->where('article_id', $v->id)->where('user_id', $v->user_id)->first();
//            if ($ArticleUp) {
//                $v->is_up = $ArticleUp->type;
//            } else {
//                $v->is_up = '';
//            }
//            $save = ArticleSave::where('article_id', $v->id)->where('user_id', $v->user_id)->first();
//            if ($save) {
//                $v->is_save = true;
//            } else {
//                $v->is_save = false;
//            }

            $article = array_merge($article, $data);
        }
        return DataReturn::ReturnJson('success', $article);
    }

Answer 2

如果 Article 表的数据量只有几千上万，这样写没有什么不好的。