优化Laravel雄辩的查询结果

Question

我在laravel-5.7中有一个家庭作业表，其中5000条记录也有通过HasMany()或HasOne() Relation提供的一些关系记录。为了获得快速结果，我尝试了许多类型的雄辩查询。但是邮递员的结果时间是10200到10700毫秒，但是当我直接把这个解说给邮递员时，我就把这个变成500到1100毫秒。我想得到它在大约800毫秒后的绑定形式，拉拉维尔资源或正常阵列。

问题是，当我试图直接展示雄辩的结果时，它大约在600到1000毫秒之间。但是当我绑定到一个数组并在邮递员中显示时，它需要6200 its，为什么？我不知道？

    $page = $req->page ?$req->page:1;  // set starting value for look query limit.
    $user = Auth::user()->student()->first();
    $studentProfile = Auth::user()->student()->first();

    // collecting all homework id that have assigned to student.
    $studentHWList =  StudentHomeWork::where("student_id",$studentProfile->id)
        ->select('home_work_id')
        ->get()->pluck('home_work_id');

   // collecting page by page of homework id.
    $hwLimitList =  Homework::where('session_code', dnc($req->require('sid')))
            ->whereIn('id',$studentHWList )
            ->where('approved', '1')
            ->select('id')
            ->orderBy('updated_at','desc')
            ->get();
    $hwIndexes = $hwLimitList->pluck('id')->forPage($page,$this->recordLimit);

    $paginated = Homework::whereIn('id', $hwIndexes)
                        ->with( "user:id,username,name",
                                'subject:id,subject_name,subject_code',
                                'approveByUser','publishBy')
                        ->with(["likes"=>function($erw){
                                 $erw->select('id','home_work_id','complete_status','likes')
                                ->where("student_id", $studentProfile->id);
                        }])
                        ->with(['comment'=>function($qur){
                            $qur->where('parent_id',0)
                                ->where('user_id',$user->id);
                        }])
                        ->orderBy('id','desc')
                        ->get( );

    if( count($paginated))
    {
        $paginationData =  customPagination('getAllHW',$hwLimitList , $page , $this->recordLimit , $user, $studentProfile  );
        return response()->json(["error"=>0,"errmsg"=>"","paginationData"=>$paginationData  ,
            "response"=>['homework_list'=>$this->customResourceHWBinding($paginated , $req )],'auth'=>userType()]);

  

private function customResourceHWBinding($queryData , $request, $user, $studentProfile )
{
    $document_list =[]; $is_seen=0; $resultData =[];
  foreach ( $queryData as  $query )
  {
      if( count($query->document)  )
      {
          foreach($query->document as $document){
              if( $document->changed_filename )
              {
                  $file=""; $fileName ="";
                  $path =env('AWS_URL')."/uploads/".dnc($request->header('dbauth'))."/".$query->session_code."/homeWorks/";
                  if(is_s3FileExist( $path.$document->changed_filename ) )
                  {
                      $fileName =$document->changed_filename;
                 
                  }
                  $document_list[] = [
                      'oname'=> $document->changed_filename,
                      'ext'=>$fileName?explode('.', $document->changed_filename):"",
                      'url'=>$file,
                      'file_url'=>$document->changed_filename?$path.$document->changed_filename:""
                  ];
              }
          }
      }

      $resultData[] =   [
      'id'=>enc($query->id),
      'ids'=>$query->id,
      'pin_user_id'=>"",
      'pin_enabled'=>0,

      'created_by'=>$query->user->name,
      'created_by_image'=>getUserImage($query->user,$query->user->privilege,$request),
      'assignment_date'=>getDateFormat($query->assignment_date,0),
      'assigment_date_edit'=>"",
      'submission_date'=>getDateFormat($query->submission_date,1),
      'submission_date_edit'=>"",
      'class_code'=>$query->class_code,
      'subject'=>$query->subject?$query->subject->subject_name:"",   
      'topic'=>$query->topic,
      'is_student_seen'=> $this->studentHWSeen($query, $user, $studentProfile),
      'updated_at'=> date('d-m-Y H:i:s' , strtotime($query->updated_at)),
      'approved'=>$query->approved,
      'approve_by'=> '',
      'can_approve'=>0,
      'comment_count'=>0,
      'total_like'=>0,
      'documents_count'=>count($document_list)?count($document_list):0,
      'is_draft'=> $query->draft?$query->draft:0,
  ];
  }
  return $resultData;
}

private function studentHWSeen( $query , $user, $studentProfile)
{
    if(count($query->studentSeen))
    {
        foreach($query->studentSeen as $seen){
            if( user->privilege == 1  )
            {
                if($seen->student_id == $studentProfile->id )
                   return 1;
            }
        }
    }
    return 0;

}

我尝试使用Resource，但它也使用3+seconds。我尝试了太多的其他优化解决方案，但在我的情况下不起作用。有人告诉使用查询生成器而不是雄辩的语言来优化查询。在这里找到Optimising Laravel query。这对我来说是个好答案吗？我不确定。请帮帮我。

请检查一下我的照片。

​

雄辩的查询结果

​

Answer 1

首先，尝试优化这一点：

$paginated = Homework::whereIn('id', $hwIndexes)
                        ->with( "user:id,username,name",'subject:id,subject_name,subject_code',
                            'approveByUser','publishBy')
                        ->with(["likes"=>function($erw){
                            $erw->select('id','home_work_id','complete_status','likes')
                                ->where("student_id",Auth::user()->student()->first()->id);
                        }])
                        ->with(['comment'=>function($qur){
                            $qur->where('parent_id',0)
                                ->where('user_id',Auth::id());
                        }])
                        ->orderBy('id','desc')
                        ->get( );

在嵌套查询：Auth::user()->student()->first()->id中运行相同的代码。

优化版本：

$studentId = Auth::user()->student()->first()->id;


$paginated = Homework::whereIn('id', $hwIndexes)
                        ->with("user:id,username,name", 'subject:id,subject_name,subject_code', 'approveByUser', 'publishBy')
                        ->with(["likes"=>function($erw) use ($studentId) {
                            $erw->select('id','home_work_id','complete_status','likes')
                                ->where("student_id", $studentId);
                        }])
                        ->with(['comment'=>function($qur) {
                            $qur->where('parent_id',0)
                                ->where('user_id',Auth::id());
                        }])
                        ->orderBy('id', 'desc')
                        ->get();

记住向在where条件下使用的字段添加索引。