jquery - 如何使用 Laravel yajra 数据表修复低速

Question

Laravel 专家！请帮我。

我是 laravel 的新手，刚刚使用 yajra 数据表制作了数据表。问题是当结果超过 2k 行时它的速度太慢。

请检查我的代码并为我提供修复它的最佳解决方案。

请注意我是初学者，请原谅我的纯编码。

我没有使用 get() 进行收集，也许 Join 和 WhereIn 子句有问题？

控制器：

public function MyController(Request $request)        
    {
    
        if(request()->ajax()) 
        {
            $season_id_array = $request -> season_id_array;
            $season_id_array = explode(",", $season_id_array);
            $league_id_array = $request -> league_id_array;
            $league_id_array = explode(",", $league_id_array);
            $weeknumber_date = $request -> weeknumber;
            
            
            $match_result = DB::table('season_match_plan as a')
                ->join('team_list as b', 'b.team_id', '=', 'a.home_team_id')
                ->join('team_list as c', 'c.team_id', '=', 'a.away_team_id')
                ->join('season as d', 'd.season_id', '=', 'a.season_id')
                ->join('league as e', 'e.league_id', '=', 'a.league_id')
                ->join('season_league_team_info as f', 
                    function($join) 
                    {
                        $join->on('a.season_id', '=','f.season_id');
                        $join->on('a.home_team_id', '=', 'f.team_id');
                    })
                ->join('season_league_team_info as g', 
                    function($join) 
                    {
                        $join->on('a.season_id', '=','g.season_id');
                        $join->on('a.away_team_id', '=', 'g.team_id');
                    })
               
                
                ->leftJoin('odds as h2',
                    function($join) 
                    {
                        $join->on('a.match_id', '=','h2.match_id');
                        $join->on('h2.bookmaker_id', '=', DB::raw('(SELECT id FROM bookmakers WHERE bookmaker_name = "highest")'));
                    }
                )
                ->leftjoin('real_price_dcl as r', 'a.DCL_refer_id', '=', 'r.id')
                -> where(
                    'a.status', '=', 'END'
                )
                
                -> where(function($q) use($season_id_array){
                   
                    $q -> orwhereIn('a.season_id', $season_id_array);
                    
                })
                -> where(function($q) use($league_id_array){
                    
                    $q -> orwhereIn('a.league_id', $league_id_array);
                    
                    
                })
               
                ->when($weeknumber_date, function($query, $weeknumber_date) {
                    $query -> where( 'c_WN', '=' ,function ($q)use($weeknumber_date){
                        $q -> select('week') ->from('date_week_map') ->where('date','=', $weeknumber_date);
                    });
                })
                -> select(
                    DB::raw('e.`league_title` AS League') , 
                    DB::raw('d.`season_title` AS Season') , 
                    DB::raw('a.`date` AS Date') , 
                    DB::raw('a.c_WN') , 
                    DB::raw(" CONCAT(b.`team_name` , ' : ', c.`team_name` ) AS Game"),
                    DB::raw("CONCAT('<a href=\"eachMatch/', TO_BASE64(a.`match_id`), '\" target=\"_blank\">', CONCAT(a.`total_home_score` , ' : ', a.`total_away_score`), '</a>') AS Score"),
                    'a.total_home_score', 'a.total_away_score',
                    DB::raw('b.`team_name` AS Home_team_name'),
                    DB::raw(' f.`S_H_ranking` AS Static_HRank'),
                    DB::raw(' a.`D_Home_RS_8` AS Dynamic_HRS_8'),
                    DB::raw(' a.`D_Home_RS_6` AS Dynamic_HRS_6'),
                    DB::raw(' a.`home_team_score` AS Home_score'),
                    DB::raw(' a.`home_team_strength` AS Home_strength'),
                    DB::raw(' c.`team_name` AS Away_team_name'),
                    DB::raw('g.`S_A_ranking` AS Static_ARank'),
                    DB::raw('a.`D_Away_RS_8` AS Dynamic_ARS_8'),
                    DB::raw('a.`D_Away_RS_6` AS Dynamic_ARS_6'),
                    DB::raw('a.`Away_team_score` AS Away_score'),
                    DB::raw('a.`Away_team_strength` AS Away_strength'),
                    DB::raw('CONCAT(f.`S_H_ranking`, " v " , g.`S_A_ranking`) AS staticRank'),
                    DB::raw('CONCAT(a.`D_Home_ranking_8`, " v ", a.`D_Away_ranking_8`) AS DynamicRank_8'),
                    DB::raw('CONCAT(a.`D_Home_ranking_6`, " v ", a.`D_Away_ranking_6`) AS DynamicRank_6'),
                    
                    DB::raw('h2.Home AS highest_Home'),
                    DB::raw('h2.Draw AS highest_Draw'),
                    DB::raw('h2.Away AS highest_Away'),

                    DB::raw('r.H_price AS Real_Home'),
                    DB::raw('r.D_price AS Real_Draw'),
                    DB::raw('r.A_price AS Real_Away')

              )
              ->orderBy('a.match_id');

            
            return DataTables()::of($match_result)
                ->editColumn('Score', function($match_result) {
                    return html_entity_decode($match_result->Score);
                })
                
                ->rawColumns(['Score'])
                
                ->make(true);
                
        }
    }

JS：

   $('#table').DataTable({
        processing: false,
        lengthMenu: [[10, 50, 100, -1], [ 10,50, 100, "All"]],
        pageLength: 100,
        destroy: true,
        serverSide: true,
        
        ajax: {
          url: "showRanking",
          type: 'POST',
          data:
          {
            _token : $('meta[name="csrf-token"]').attr('content'),
            season_id_array : season_id_array,
            league_id_array : league_id_array,
            weeknumber : weeknumber,
            
          }
          
          },
          columns: [
                
                { data: 'League',  name: 'League', width: '200px' },
                { data: 'Season', name: 'Season' },
                { data: 'Date', name: 'a.Date' },
                { data: 'c_WN', name: 'c_WN' },
                { data: 'Game', name: 'Game', width:'200px'},
                { data: 'Score', name: 'Score' },
                { data: 'Home_team_name', name: 'Home_team_name'},
                { data: 'Static_HRank', name: 'Static_HRank' },
                { data: 'Dynamic_HRS_8', name: 'Dynamic_HRS_8'},
                { data: 'Dynamic_HRS_6', name: 'Dynamic_HRS_6'},
                { data: 'Home_score', name: 'Home_score'},
                { data: 'Home_strength', name: 'Home_strength'},
                { data: 'Away_team_name', name: 'Away_team_name'},
                { data: 'Static_ARank', name: 'Static_ARank' },
                { data: 'Dynamic_ARS_8', name: 'Dynamic_ARS_8'},
                { data: 'Dynamic_ARS_6', name: 'Dynamic_ARS_6'},
                { data: 'Away_score', name: 'Away_score'},
                { data: 'Away_strength', name: 'Away_strength'},
                { data: 'staticRank', name: 'staticRank'},
                { data: 'DynamicRank_8', name: 'DynamicRank_8'},
                { data: 'DynamicRank_6', name: 'DynamicRank_6'},
                

                { data: 'highest_Home', name: 'highest_Home'},
                { data: 'highest_Draw', name: 'highest_Draw'},
                { data: 'highest_Away', name: 'highest_Away'},

                { data: 'Real_Home', name: 'Real_Home'},
                { data: 'Real_Draw', name: 'Real_Draw'},
                { data: 'Real_Away', name: 'Real_Away'},

          ],
          
      }
      );

所以排序和搜索也不适用于 Front。

请帮助我，我是 Laravel 初学者。

此致。

标签： jquerylaraveldatatableyajra-datatable